SGN SGN - 1 month ago 7
C# Question

Multiple where conditions in EF using Lambda expressions

I'm using entity framework and Linq Querable, I want to select some data using a multiple where clause.

In my DB, i have in one field that may have multiple ids

FieldOfInterestID
, separated by ";" (I know, I know, but it's to late to do something), or just one id, that means that the comma won't be present

I want to split the string with the ";" separator, and than all those ids to use them in my where clause.

My code looks something like this:

await ctx.Customer.AsNoTracking()
.Where(e => e.UserId == userId)
.Select(e => new UserDTO {
FieldsOfStudy = ctx.Terms.Where(t => {
if (!e.FieldOfInterestID.Contains(";") && t.TermId.ToString() == e.FieldOfInterestID)
return true;
else if (e.FieldOfInterestID.Contains(";")

{
string fieldOfInterestIds = e.FieldOfInterestID.Split(";");
foreach (string fieldOfInterestID in fieldOfInterestIds)
{
if (t.TermId.ToString() == e.FieldOfInterestID)
return true;
else
return false;
}
}
else
return false;
}
})
.ToListAsync().ConfigureAwait(false);


My current "error" is
not all code paths return a value...


How can I use string[] in my where clause better?

Answer

The current error is because of this branch:

foreach (string fieldOfInterestID in fieldOfInterestIds)
{
    if (t.TermId.ToString() == e.FieldOfInterestID)
        return true;
    else
        return false;
}

From the compiler perspective, the body of the foreach may not be entered, hence not all code paths return a value....

You can fix it by moving the return false; outside the body:

foreach (string fieldOfInterestID in fieldOfInterestIds)
{
    if (t.TermId.ToString() == e.FieldOfInterestID)
        return true;
}
return false;

This will fix the compiler error, but will not solve the problem. Once you run it you'll find that LINQ to Entities does not support lambda expressions with body (=> { ... }), and also string.Split method.

The real solution requires a different criteria - instead of unsupported

e.FieldOfInterestID.Split(";").Contains(t.TermId.ToString())

the opposite but supported (uses string concatenation and string.Contains)

(";" + e.FieldOfInterestID + ";").Contains(";" + t.TermId + ";")

Enclosing both strings with ; is needed to correctly handle first, intermediate and last tokens.

The final query could be like this:

var query = ctx.Customer.AsNoTracking()
    .Where(e => e.UserId == userId)
    .Select(e => new UserDTO
    {
        FieldsOfStudy = ctx.Terms
            .Where(t => (";" + e.FieldOfInterestID + ";").Contains(";" + t.TermId + ";"))
    });
Comments