CODEWITHSUNDEEP
.netsqllinqlinq-to-sql
Ronak
Ronak

Reputation: 19

Filtering using LINQ to SQL

I have one table as below

ID Code  
1  9198  
2  9194  
3  91989  
4  91900  
5  918907

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.

Upvotes: 0

Views: 154

Answers (2)

Adrian Iftode
Adrian Iftode

Reputation: 15683

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

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1503419

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.

Upvotes: 1

Related Questions