Linq to SQL - Query

Question

I am trying to mimic below statement in Linq to SQL.

WHERE (rtrim(posid) like '%101' or rtrim(posid) like '%532')

I statement basically determine if posid ends with 101 or 532. In the above example I am only making 2 comparisons but their could be 1 to N comparisons all joined with OR. I store the comparison values (101,532,...) in a generic list that I send to my Linq to SQL method.

I have tried to mimic above SQL using a where clause unsuccessfully (example below):

var PosNum = new List<string>();
PosNum.Add("101");
PosNum.Add("532");
var q = (from a in context.tbl_sspos select a);
q = q.Where(p => PosNum.Contains(p.posid.Trim()));

The issue with the above where clause is that it tries to do an exact match rather I want an ends with comparison.

How would I mimic the SQL statement in Linq to SQL. Thank You in advance for any help / advice you can provide.

This is a duplicate of http://stackoverflow.com/questions/835790/how-to-do-sql-like-in-linq — Justin Pihony, Mar 14 '12 at 16:35

KingCronus · Answer 1 · 2012-03-14T16:44:25.803

1

I would use String.EndsWith();

This will check the end of the string rather than entire contents of it.

var q = (from a in context.tbl_sspos select a);
q = q.Where(p => p.posid.EndsWith("102") || p.posid.EndsWith("532"));

edited Mar 14 '12 at 16:44

answered Mar 14 '12 at 16:35

KingCronus

4,509
1
24
49

How many PosNums are you likely to have? Doing it this way is not going to scale well. – KingCronus Mar 14 '12 at 16:40
Sorry I can't find a EndsWith Method in the Generic List. – Victor Mar 14 '12 at 16:41
If you only have a few items you can just chain them as in my example above I believe. – KingCronus Mar 14 '12 at 16:42

ntziolis · Accepted Answer · 2012-03-14T18:02:57.113

0

In EF 4 you can use the StartsWith / EndsWith methods by now. Might also work in LINQ to SQL.

UPDATE
Just realized that you are trying todo this against multiple values (PosNum), I don't think that this is directly supported currently. You can however concatenate multiple Where()clauses to get the result.

UPDATE 2
As AdamKing pointed out concatenating the where clauses was filtering against all PosNum values, here is the corrected version:

var baseQuery = (from a in context.tbl_sspos select a);    
IEnumerable<YourType> q = null;

foreach(var pos in PosNum)
{
    if(q == null)
        q = baseQuery.Where(a => a.posid.EndsWith(pos));
    else
        q = q.Union(baseQuery.Where(a => a.posid.EndsWith(pos)));
}

This is not as pretty anymore, but works nonetheless.

edited Mar 14 '12 at 18:02

answered Mar 14 '12 at 16:37

ntziolis

10,091
1
34
50

Do you have an example for concatenating multiple Where()clauses – Victor Mar 14 '12 at 16:44
This example doesn't have the desired effect does it? Off the top of my head here you will look for only results that end in all members of PosNum, i.e. none? – KingCronus Mar 14 '12 at 16:45
The sample is doing exactly that, it is concatenating the `Where()` clause by reassigning q in the `foreach` – ntziolis Mar 14 '12 at 16:46
@AdamKing As far as I understand that's exactly what he wants, no? – ntziolis Mar 14 '12 at 16:48
You have lost me here...for example if a result ends in 101, this would match in the first iteration, then result in no matches in the second wouldn't it? – KingCronus Mar 14 '12 at 16:48
@AdamKing you absolutely correct, my bad, I'll update it right away – ntziolis Mar 14 '12 at 16:49
Hi If you change your q.Concat to q.Intersect the results would be correct for all cases. Thanks for your help! I wonder if I should just scrap link to SQL and dynamically create a SQL statement in code. – Victor Mar 14 '12 at 17:12
@Victor Ah yeah thx for the tip, didn't think of the cases where 2 PosNum entries could actually match the same id like: `id = 0123`and posnums: `123` and `23`, nice catch – ntziolis Mar 14 '12 at 17:17
Sorry did some more testing and q.Intersect is not correct. Your original solution using q.Concat is the best solution. Although q.Concat may produce duplicates if posnums match the same id. – Victor Mar 14 '12 at 18:00
@Victor No problem, actually I looked it up again, and the one we were looking for is `Union` since this one is getting rid of duplicates after doing the `Concat`, checkout this post: http://gehirnwindung.de/post/2010/05/12/LINQ-Extension-Methods-Union.aspx – ntziolis Mar 14 '12 at 18:04

Linq to SQL - Query

2 Answers2