Filtering using LINQ to SQL

Question

I have one table as below

I have this table ready now what i want to do is that like i have one value 919898989898 and from this value i get the below result from the table

Result  
ID Code  
1  9198

and if i pass 91989000155363 then result should be

Result  
ID Code  
3  91989

i want this kind of query in linq to sql that will match maximum char of given value match with database table.

So you want an analog of Code.StartsWith(x) in C#, or Code LIKE '%' + x in SQL? — abatishchev
– abatishchev, Commented Jan 21, 2012 at 9:03
Remember that any LINQ to SQL call should be translatable to SQL, otherwise you will need to load all the data into memory and process it by LINQ to Objects — abatishchev
– abatishchev, Commented Jan 21, 2012 at 9:04

Jon Skeet · Accepted Answer · 2012-01-21 09:04:53Z

1

Assuming that code is actually a VARCHAR, you could try:

var input = "919898989898";
var allMatches = from item in dbContext
                 where input.StartsWith(item.Code)
                 orderby item.Code.Length descending
                 select item;
var longestMatch = allMatches.FirstOrDefault();

Or putting the query into a single statement, using dot notation instead of query expressions:

var longestMatch = dbContext.Where(item => input.StartsWith(item.Code))
                            .OrderByDescending(item => item.Code.Length)
                            .FirstOrDefault();

Now those queries are okay logically - but you'll have to try them to see whether they're correctly translated to SQL.

answered Jan 21, 2012 at 9:04

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ronak Over a year ago

i tried that and get the below error. Only arguments that can be evaluated on the client are supported for the String.StartsWith method.

Jon Skeet Over a year ago

@Ronak: In that case I'm not even sure it can be done in LINQ to SQL.

Adrian Iftode · Accepted Answer · 2012-01-21 17:07:20Z

1

var query = from item in dbContext
     where item.Code == input.Substring(0, item.Code.Length)
     orderby item.Code.Length descending
     select item;

var longestMatch = query.FirstOrDefault();

answered Jan 21, 2012 at 17:07

Adrian Iftode

15.7k4 gold badges51 silver badges73 bronze badges

Collectives™ on Stack Overflow

Filtering using LINQ to SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related