JPAK

I spent some time last night trying to implement a query having a LIKE expression as well as SKIP and TAKE expressions with parameter for Sql server 2000.

I could not have Linq work for the LIKE expression, so I tried an Object query with LIKE, SKIP and LIMIT to find that LIMIT did not work with parameters on Sql 2000.

So I ended up mixing Object query and Linq in the same expression, it seems to work but produces some rather complex SQL.

I thought Linq to Entities turned the Linq expression tree into e SQL, so how could it solve a problem that Object query could not solve

Code Block

List<User> users = db.Users.Where("it.UserName LIKE @usernameToMatch",

new ObjectParameter("usernameToMatch", usernameToMatch))

.OrderBy(u => u.UserName).Skip(pageIndex * pageSize).Take(pageSize).ToList();

Is there a better way to do this

Will this work with other providers when they are availble



Re: ADO.NET (Pre-release) Linq to entities and LIKE

Michael Pizzo - MSFT

JPAK;

In LINQ, you can use "Contains", "StartsWith", or "EndsWith" similar to "LIKE". For example, against Northwind you could do the following:

Code Block

using (NorthwindEntities db = new NorthwindEntities())

{

string name = "Restaurant";

int skip = 0;

int take = 10;

var q = (from cus in db.Customers

where cus.CompanyName.Contains(name)

select cus).OrderBy(c => c.CompanyName).Skip(skip).Take(take);

foreach (Customer c in q)

Console.WriteLine("{0} {1}", c.CompanyName, c.ContactName);

}

Console.ReadLine();

Yes, as you discovered, going from an eSQL string/builder-based ObjectQuery<T> to a LINQ expression is supported (in fact, db.Customers does a CreateQuery<Customer>("[Customers]") under the covers, which is simply an eSQL shortcut for "SELECT VALUE customer FROM [Customers] AS customer"). However, it is not possible to call additional query builder methods once you've built a LINQ expression.

Note that LINQ to Entities does not create an eSQL string, but directly builds a canonical command tree which is passed to the provider, just as eSQL is parsed and passed to the provider as a canonical command tree. LINQ is generally more restrictive in what is supports than eSQL, although in some cases LINQ exposes nice "sugar" functions to simplify generation of more complex SQL strings.

HTH,

-Mike






Re: ADO.NET (Pre-release) Linq to entities and LIKE

Kati Iceva - MSFT

Adding to Mike¡¯s respond.

Your observation that Skip and Limit with parameters are not supported over SQL Server 2000 is correct. This is because SQL Server 2000 does not support TOP (@parameter) and we rely on TOP when translating Skip and Limit.

The reason your Linq query works is that the expressions ¡°pageIndex * pageSize¡± and ¡°pageSize¡± given as arguments to Skip and Take are evaluated on the client, not sent as parameters.

Thanks,

Kati






Re: ADO.NET (Pre-release) Linq to entities and LIKE

JPAK

Thank you Mike,

I know I could have used StartWith or Contains, but I am working on a membership provider and the methods pass the %wildcard as part of the parameter, so it was not an option.

One more question:

Will the next versions of the designer be using pluralization or sigularization to name Entities and EntitySets

Right now, if I have a table named Users, both the entity and the EntitySet are named Users which is quite confusing at first.

So we have to go manually rename all Entities to the singular form.

Even if it did not take into account the irregular forms of pluralization at first, it would be nice to have somthing like:

If Table Name ends with 's'

If Table Name ends with 'ies'

use Table Name for EntitySet and remove the ''ies' and add a 'y' for Entity

Else

use Table Name for EntitySet and remove the 's' for Entity

Else

If Table Name ends with 'y'

remove 'y' and add 'ies' for EntitySet and use Table Name for Entity

Else

add 's' for EntitySet and use Table Name for Entity

All we would have to deal with manually is the Person, Octopus, Caribou.... tables





Re: ADO.NET (Pre-release) Linq to entities and LIKE

Michael Pizzo - MSFT

Pluralization (and "Singularization") is one of the features we looked hard at for the designer (and underlying API) but had to cut for version 1. The problem is not with the code (we actually have a couple of routines that are pretty advanced that we could borrow from) but with testing all of the different variations.

There is also the issue that the pluralization rules would be english-language based (so we'd need a way to disable them for other languages), but we still believe even an english-only version would be useful.

The only saving grace is that the designer does make it relatively easy to rename entities, but this is still one of my top 5 features that I wish we could have gotten in for version 1 (but leaves us something to do for V2... :-)