EntityFramework: Linq on SQL: Contains or IndexOf?

Question

EntityFramework: Linq on SQL: Contains or IndexOf?

c# entity-framework entity-framework-6 linq-to-sql

Question

I have a weird situation regarding EntityFramework 6 with .NET 4.5 (C#).

I have (almost) the same query in two different places. But one time it queries agains the database and the second time it queries against in-memory objects. And since I'm filtering for a substring, this is a crucial difference:

Database structure are tables Role, Right and a cross-table Role_Right

First time around I want to find all available rights that are not already assigned to the role plus (and that's where it gets complicated) a manual filter to reduce the result list:

Role role = ...;
string filter = ...;
var roleRightNames = role.Right.Select(roleRight => roleRight.RightName);
var filteredRights = context.Right.Where(right => !roleRightNames.Contains(right.RightName));
if (!string.IsNullOrWhiteSpace(filter))
{
    filteredRights = filteredRights.Where(e => e.RightName.Contains(filter));
}
var result = filteredRights.ToList();

I cannot use IndexOf(filter, StringComparison.InvariantCultureIgnoreCase) >= 0) because this cannot be translated to SQL. But I'm fine with Contains because it produces the desired result (see below).

When enabling the SQL output I get:

SELECT [Extent1].[RightName] AS [RightName]
FROM [dbo].[Right] AS [Extent1]
WHERE ( NOT ([Extent1].[RightName] IN ('Right_A1', 'Right_A2', 'Right_B1'))) AND ([Extent1].[RightName] LIKE @p__linq__0 ESCAPE '~'
-- p__linq__0: '%~_a%' (Type = AnsiString, Size = 8000)

Which is exactly what I want, a case-insensitive search on the filter "_a" to find for example 'Right_A3'

The second time I want to filter the existing associated rights for the same filter:

Role role = ...;
string filter = ...;
var filteredRights = string.IsNullOrWhiteSpace(filter)
    ? role.Right
    : role.Right.Where(e => e.RightName.IndexOf(filter, StringComparison.InvariantCultureIgnoreCase) >= 0);            
var result = filteredRights.ToList();

This time it forces me to use IndexOf because it uses the Contains method of the string instead of translating it to an SQL LIKE and string.Contains is case-sensitive.

My problem is that I cannot - from looking at the code - predict when a query is executed against the database and when it is done in-memory and since I cannot use IndexOf in the first query and Contains in the second this seems to be a bit unpredictable to me. What happens when one day the second query is executed first and the data is not already in-memory?

Edit 10 Feb 2020

OK, so I figured out what the main difference is. context.Right is of type DbSet which is an IQueryable and so is the subsequent extension method Where. However userRole.Right returns an ICollection which is an IEnumerable and so is the subsequent Where. Is there a way to make the relationship property of an entity object to an IQueryable? AsQueryable did not work. Which means that all associated Right entities are always gotten from the database before doing an in-memory Where. We're not talking about huge amounts of data and at least now this behaviour is predictable, but I find it unfortunate nonetheless.

Dee J. Doena

Related Questions

View more on Stack Overflow

Dee J. Doena · Accepted Answer · 2/10/2020 2:27:58 PM

OK, so I found two different solutions to always query against the database in case a relation contains a huge result set. Both solutions are not directly intuitive - IMHO - and you will need the DbContext variable which you hadn't needed before.

Solution one is using the Role table as a starting point and simply filtering for the entity with the correct Id. Note You cannot use Single because then you deal with a single entity object and you're right back where you've started. You need to use Where and then a SelectMany even though it's counter-intuitive:

Role role = ...;
string filter = ...;
var filteredRights = context.Role.Where(e => e.RoleId == userRole.RoleId).SelectMany(e => e.Right);
if (!string.IsNullOrWhiteSpace(filter))
{
    filteredRights = filteredRights.Where(e => e.RightName.Contains(filter));
}
var rights = filteredRights.ToList();

which results in an SQL query against the DB:

SELECT 
    [Extent1].[RightName] AS [RightName]
    FROM [dbo].[Role_Right] AS [Extent1]
    WHERE ([Extent1].[RoleId] = @p__linq__0) AND ([Extent1].[RightName] LIKE @p__linq__1 ESCAPE '~')
-- p__linq__0: '42' (Type = Int32, IsNullable = false)
-- p__linq__1: '%~_a%' (Type = AnsiString, Size = 8000)

The second solution I found here: https://stackoverflow.com/a/7552985/2334520

In my case this results in:

Role role = ...;
string filter = ...;
var filteredRights = context.Entry(userRole).Collection(e => e.Right).Query();
if (!string.IsNullOrWhiteSpace(filter))
{
    filteredRights = filteredRights.Where(e => e.RightName.Contains(filter));
}
var rights = filteredRights.ToList();

and SQL

SELECT 
    [Extent1].[RightName] AS [RightName]
    FROM [dbo].[Role_Right] AS [Extent1]
    WHERE ([Extent1].[RoleId] = @EntityKeyValue1) AND ([Extent1].[RightName] LIKE @p__linq__0 ESCAPE '~')
-- EntityKeyValue1: '42' (Type = Int32, IsNullable = false)
-- p__linq__0: '%~_a%' (Type = AnsiString, Size = 8000)

EntityFramework: Linq on SQL: Contains or IndexOf?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related