Using Contains() in Linq to SQL

Using contains() in LINQ to SQL

Looking at the other attempts saddens me :(

public IQueryable<Part> SearchForParts(string[] query)
{
  var q = db.Parts.AsQueryable(); 

  foreach (var qs in query)
  { 
    var likestr = string.Format("%{0}%", qs);
    q = q.Where(x => SqlMethods.Like(x.partName, likestr));
  }

  return q;
}

Assumptions:

• partName looks like: "ABC 123 XYZ"

• query is { "ABC", "123", "XY" }

Best way to use LINQ to SQL Contains() between a local List and a table

LINQ to SQL or LINQ to EF aren't magic. They can't do anything that can't be expressed in SQL. Nor will they rewrite bad queries into a better form before generating SQL statments.

Unfortunately, when L2S (and some early versions of EF Core) can't generate the proper SQL statement, they'll try to load the rows and process them on the client resulting in very inefficient queries.

The way one would query items based on a list of IDs is to write :

select ID
from Members
Where ID in (1,2,6,7,9)

LINQ to EF/SQL can generate an IN clause only when Enumerable.Contains is used on a list of IDs, eg :

var ids=new[]{1,2,6,7,9};
var actualIDs = db.MemberTable.Where(x=>ids.Contains(x.ID));

The first query does that, in a verbose way. It's essentially the same as writing

var ids=members.Select(s => s.MemberId);
var items = db.MemberTable.Where(x => ids.Contains(x.MemberId) && x.StoreId == storeId)
                          .Select(i => i.MemberId)
                          .ToList();

This should generate this query :

SELECT MemberID
From Members
Where StoreId=12354 and MemberID in (1,2,5,8,9...)

This is a fast query, especially if MemberID and StoreID are indexed.

If LINQ to SQL is used, adding a ToArray() or ToList() to the local query may be needed. L2S was very finicky to say the least, and never meant to be anything more than a technology demonstration. Local performance is the same anyway, in both cases all IDs need to be read to construct the IN clause :

var ids=members.Select(s => s.MemberId).ToArray();

The second query is probably very slow - it's not apparent what it does because everything is written in a single line and there's no way to see which part is the database query and which part is the local query. Rewriting it :

var items2 = members.Where(x => 
                  db.MemberTable
                     .Where(c => c.StoreId== storeId)
                     .Select(s => s.MemberId)
                .Contains(x.MemberId))
              .Select(i => i.MemberId)
              .ToList();

Shows that this executes this database query once for each item in the members list :

db.MemberTable
  .Where(c => c.StoreId== storeId)
  .Select(s => s.MemberId)

One would have to trace the SQL statements, but I suspect this is going to execute the following query over and over :

select MemberID
from Members
where StoreID=12345

It would be better (but not good) to rewrite this as :

var dbIDs=b.MemberTable
           .Where(c => c.StoreId== storeId)
           .Select(s => s.MemberId)
           .ToArray();
var items2 = members.Where(x => dbIds.Contains(x.MemberId))
              .Select(i => i.MemberId)
              .ToList();

This will still load unneeded rows and take extra database locks but at least it will do so only once.

get the data using contains() in linq to sql query

Here you can find list of LINQ limitations. Your query falles under 'The left side of the clause must be an attribute' rule...

If customVendorIDs are also retrieved with LINQ query, you can work around this limitation by joining tables with vendor filter to get only desired plus_visitSet records.

Please note that with crm.plus_visitSet. ** tolist() ** ... query you are retreiving all plus_visit records from database and then filtering this out. It could be significant hit on performance....

using .Contains() with linq-to-sql

What you have is correct. This will be translated into an IN clause in SQL with the values supplied in the collection.

On an unrelated note, you should try ordering the query by date descending and use FirstOrDefault(). As it is now, you're going to bring back the entire result set and throw away every row but one.

How do you force a .Contains in a Linq to SQL where clause to be done by SQL server?

As Evk commented, the AsEnumerable() caused the where to be executed locally, not on the SQL server.

It was being used since I needed lists and dictionaries in the returned type which cause exceptions if you try to build them in an IQueryable.

I altered the query to

using (var db = new DataContext(dbConnectionString))
{
   ids = new list<long> {"1","2",...};
   var data = (from item in db.GetTable<dataTable>()
               where ids.Contains(item.ID)
               select new 
               {
                  name = item.name,
                  subIds = item.subitemIDs
                  ...
               }).AsEnumerable()
               .Select(x=> new customDataStructure
                   {
                      itemname = x.name,
                      subIds = x.subIds.ToList(),
                      ...
                   }).ToList();
}

It let the data collection be done by SQL and then have the needed structure completed after the SQL executes.

Why does LINQ to Entities string.Contains(string.Empty) match anything but string.Contains(string.Empty.ToLower()) match nothing?

I believe this would be a quirk of the SQL Server provider for EF in that when you perform the .ToLower() on the criteria and the field being compared it is recognizing the request as explicitly case-insensitive and replaces the LIKE query with the CHARINDEX comparison which does not handle empty strings in SQL Server the same way. The behaviour for case sensitivity will depend on the database engine, and in the case of SQL Server, the collation selected for strings in the database. Not sure why a LOWER(Tracking_Number) LIKE LOWER('%%') couldn't have been used.

Personally, when composing EF Linq expressions, my querying code will always inspect for IsNullOrEmpty on strings and not append .Where() conditions where an actual criteria has not been supplied. This way WHERE clauses are only applied for provided criteria.

I.e. If I trust the DB won't be collated case-sensitive:

if(!string.IsNullOrEmpty(substring))
    query = query.Where(entity => entity.Tracking_Number.Contains(substring));

if I am concerned that the database could be collated case-sensitive:

if(!string.IsNullOrEmpty(substring))
    query = query.Where( entity => entity.Tracking_Number.ToLower().Contains(substring.ToLower()));

Even there I would prefer to set a standard that Tracking_Number is always stored as a lower-case value if the database serves solely this application. The entity properties would enforce that any set value is lower-cased. (removing the need for .Tracking_Number.ToLower() in the queries.)

Use contains in LINQ to SQL join

You can't use like in a Linq join. In fact, you can't use like in Linq at all, only conventional string methods like StartsWith, EndsWith, or Contains.

You'd have to do something like this:

var query =
    from f in db.form
    from n in db.name.Where(x => f.nameField.Contains(x.firstName))
    ...

Linq To SQL Contains

Translation to LINQ is generally pretty straightforward except for one special trick in the case of your query. You can translate your select, where, and from statements in a natural way as shown below. In the case of an IN statement though, you have to get the results from the inner subquery first, and then check if the inner subquery .Contains the value you want to check.

var groups =
   (from gu in GroupUser
    where gu.UserId == 169
    select gu.GroupId).ToList();

var result = 
    from p in GroupContentPermission
    join c in Content on p.ContentId equals c.ContentId
    where groups.Contains(p.GroupId)
    select new { c.Content, c.ListOrder, c.ContentTypeID, c.ContentId };

// result should contain the same results as the SQL query

Here are some other resources you may find helpful as well (you can find many more resources and tutorials on LINQ if you do a quick google search. There are literally thousands):

• Linqer, a SQL to LINQ converter.
• LinqPAD, a simple .NET/LINQ tester for rapid experimentation
• ScottGu's definitive guide to Using LINQ To SQL
• Related SO question: What are some good LINQ resources?, which references a tutorial called 101 LINQ Samples.

---

Related Topics