CODEWITHSUNDEEP
c#sqllinq
Skram
Skram

Reputation: 103

LINQ SQL Select from list using partial match

I have a list wbsList containing the current elements:

SS-B23813
SS-B23814

I want the SQL lookup to retreive all wbs elements that starts with those numbers to be listed, so I use this code:

var q =
    from a in MESdb.GetTable<t_SAP_Order>()
    where wbsList.Contains(a.WbsElement) 
    orderby a.WbsElement, a.OrderDescription
    select a;

This results in nothing, because it only shows exact matches. All my wbs'es has a longer string (SS-B23813-24-1-15-06-100) How can I use the list as a partial search criteria?

UPDATE:

When I change the code to Dunth's answer, I get the following error: Local sequence cannot be used in LINQ to SQL implementations of query operators except Contains operator.

I wonder if this error comes because of some error when I try to display the result in a datagrid:

caseGrid.DataSource = q.Select(o => new
                    {
                        Workcenter = o.MainWorkCenter,
                        SO = o.Ordr,
                        Description = o.OrderDescription,
                        SerialNumber = o.SerialNumber,
                        BasicFinish = o.BasicFin
                    }).ToList();

Upvotes: 0

Views: 1020

Answers (2)

Dhunt
Dhunt

Reputation: 1594

Try this to find where it might not be at the start.

var q = MESdb.GetTable<t_SAP_Order>()
            .Where(a => wbsList.Any(b => a.WbsElement.Contains(b)))
            .OrderBy(a => a.WbsElement)
            .ThenBy(a => a.OrderDescription).ToList();

Upvotes: 1

Crowcoder
Crowcoder

Reputation: 11514

Contains does a sql IN(...), you want a sql LIKE so use wbList.StartsWith(a.WbsElement)

Upvotes: 0

Related Questions