CODEWITHSUNDEEP
c#linq
Andrew
Andrew

Reputation: 1621

Proper way to use LINQ for this type of query?

I was originally using a foreach loop and then for each element in the loop, I perform a LINQ query like so:

foreach (MyObject identifier in identifiers.Where(i => i.IsMarkedForDeletion == false))
{
    if (this.MyEntities.Identifiers.Where(pi => identifier.Field1 == pi.Field1 && identifier.Field2 == pi.Field2 && identifier.Field3 == pi.Field3).Any())
    {
        return false;
    }
}

return true;

Then I modified it like so:

if (identifiers.Any(i => !i.IsMarkedForDeletion && this.MyEntities.Identifiers.Where(pi => i.Field1 == pi.Field1 && i.Field2 == pi.Field2 && i.Field3 == pi.Field3).Any()))
{
   return false;
}

return true;

My question is this still the wrong way to use LINQ? Basically, I want to eliminate the need for the foreach loop (which seems like I should be able to get rid of it) and also make the DB query faster by not performing separate DB queries for each element of a list. Instead, I want to perform one query for all elements. Thanks!

Upvotes: 1

Views: 103

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205629

Unfortunately your modified version will be executed exactly the same way (i.e. multiple database queries) as in the original foreach approach because EF does not support database query with joins to in memory collection (except for primitive and enumeration type collections), so if you try the most logical way

bool result = this.MyEntities.Identifiers.Any(pi => identifiers.Any(i =>
    !i.IsMarkedForDeletion &&
    i.Field1 == pi.Field1 && i.Field2 == pi.Field2 && i.Field3 == pi.Field3));

you'll get

NotSupportedException: Unable to create a constant value of type 'YourType'. Only primitive types or enumeration types are supported in this context.

The only way to let EF execute a single database query is to manually build a LINQ query with Concat per each item from in memory collection, like this

IQueryable<Identifier> query = null;
foreach (var item in identifiers.Where(i => !i.IsMarkedForDeletion))
{
    var i = item;
    var subquery = this.MyEntities.Identifiers.Where(pi => 
        pi.Field1 == i.Field1 && pi.Field2 == i.Field2 && pi.Field3 == i.Field3);
    query = query != null ? query.Concat(subquery) : subquery;
}
bool result = query != null && query.Any();

See Logging and Intercepting Database Operations of how to monitor the EF actions.

Upvotes: 0

Michael
Michael

Reputation: 1473

You can change your code in this way, and it will be converted to SQL statement as expected. To prevent runtime errors during transformation, it will be better to save DBSet to the IQueryable variable; identifiers should be IQueryable too, so you should change your code into something like this (to be honest, Resharper converted your foreach in this short labda):

IQueryable<MyObject2> identifiers = MyEntities.Identifiers.Where(i => i.IsMarkedForDeletion == false);
IQueryable<MyObject2> ids = MyEntities.Identifiers.AsQueryable();
return identifiers.All(identifier => !ids.Any(pi => identifier.Field1 == pi.Field1 && identifier.Field2 == pi.Field2 && identifier.Field3 == pi.Field3));

If identifiers is in memory collection you can change code in this way (hope that fields are string):

IQueryable<MyObject2> ids = MyEntities.Identifiers.AsQueryable();
string[] values = identifiers.Where(i => i.IsMarkedForDeletion == false).Select(i => String.Concat(i.Field1, i.Field2, i.Field3)).ToArray();
return !ids.Any(i => values.Contains(i.Field1 + i.Field2 + i.Field3));

Upvotes: 1

Pieter Jordaan
Pieter Jordaan

Reputation: 36

I would use it as follows:

if (identifiers.Where(i => !i.IsMarkedForDeletion &&     
this.MyEntities.Identifiers.Field1 == i.Field1 &&
this.MyEntities.Identifiers.Field2 == i.Field2 && 
this.MyEntities.Identifiers.Field3 == i.Field3).Any()))
{
   return false;
}

return true;

I hope this helps. Even though it is more to type out, it is more understandable and readable then using multiple 'where' statements.

Upvotes: 0

Related Questions