CODEWITHSUNDEEP
c#linqsql-server-2008entity-framework
joe_coolish
joe_coolish

Reputation: 7259

Linq to Entities LIKE '[A-G]%' query

I'm trying to recreate some SQL queries I have into L2E statements. One of the queries is:

SELECT TOP 25 itms.[StringValue]
      ,itms.[Name]
      ,itms.[Picture]
  FROM [DB].[dbo].[Items] AS itms
  WHERE itms.StringValue LIKE '[A-G]%'

I am given a start char (A in this case) and an end char (G) and I need to pull all of the items whos StringValue starts with a char between Start and Finish.

My first try was to see if L2E would just give it to me with this query:

items = from i in items
        where i.StringValue.StartsWith("[" + refinement.Value + "]")
        select i;

I figured it wouldn't work, but it was worth a try.

Upvotes: 0

Views: 605

Answers (2)

James Manning
James Manning

Reputation: 13579

Couple of options that should work:

1) if you know the filter is just based on the first char, then you could do something like:

var startChar = 'A';
var endChar = 'G';

// other options, but you get the point
var validStartChars = Enumerable.Range(0, 26)
    .Select(i => (char)('A' + i))
    .Where(c => c >= startChar && c <= endChar)
    .ToArray();

var matches = items.Where(i => validStartChars.Contains(i.StringValue[0]));

similarly, you could fetch the first char and just do the greater/lesser checks in the Where clause (if it's always a range like that)

2) if you want, you can still use sql to query with L2E, just use the ExecuteStoreQuery and let it return the entities back for you:

http://msdn.microsoft.com/en-us/library/dd487208.aspx

If you want the returned entities to be tracked, make sure to call the overload that lets you specify the entity set name:

http://msdn.microsoft.com/en-us/library/dd487226.aspx

Upvotes: 1

ForbesLindesay
ForbesLindesay

Reputation: 10712

That should really have worked so I'm not entirely sure why it hasn't. Worth checking that refinement.Value comes from somewhere sensible and not directly another LINQ to SQL query.

In addition to that though, there is the SQLMethods class which offers a great many of the facilities which would be available in SQL when writing LINQ to SQL queries (note that it won't work with LINQ to objects Queries). You could therefore use the SQLMethods Like method and give it your original LIKE statement.

Upvotes: 0

Related Questions