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.

Answer 1

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.

Answer 2

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"));