Dynamic LINQ instead of ExecuteSqlRaw

Question

Because I'm going to use Entity Framework Core, I'm trying to convert a SqlRaw statement

            var sql = @$"
                         update ItemList
                         set flag = 1
                         where
                             flag = 0 and
                            {groupingField} in (select {groupingField} from ItemList
                                                    where ID in (select itemID from selectedItems))
            ";
            int noOfRowsAffected = DbContext.ExecuteSqlRaw(sql);

into a sequence of LINQ statements.

            var ids = DbContext.selectedItems
                      .Select(x => x.itemID).ToList();

            var groupIds = DbContext.ItemList
                           .Where(p => ids.Contains(p.Id) && p.flag == 0
                           .Select(p => p.GetType().GetProperty(groupingField).GetValue(p)).ToList();

            var rows = DbContext.ItemList
                .Where(p => groupIds.Contains(p.GetType().GetProperty(groupingField).GetValue(p)))
                .ToList();

            foreach(var row in rows)
            {
                row.flag = 1;
            }

When I execute the statments I catch an exception on the third statement (var rows = ...):

The LINQ expression 'DbSet<ItemList>
.Where(t => __groupIds_0.Contains(t.GetType().GetProperty(__groupingField_1).GetValue(t)))' could not be 
translated. Either rewrite the query in a form that can be translated, or switch to client evaluation 
explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). 
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

How do I rewrite the statement that it can be translated?

So, you need just LINQ query and then you will update all loaded entities by yourself? Do you have feeling that you will slowdown this function cardinally? — Svyatoslav Danyliv, May 31 '21 at 17:39
I'm aware, that performance will slow down. I didn't mention that I have to use Entity Framework Core. Therfore SqlRaw has to be eliminated. — Zeneri, Jun 02 '21 at 07:43
`ItemList` has some base class? Because access to `flag` should be generalized too. — Svyatoslav Danyliv, Jun 02 '21 at 08:53

score 0 · Accepted Answer · answered Jun 03 '21 at 11:26

The following extension is help to do what you want and even supports IEnumerable and IQueryable items:

var ids = DbContext.selectedItems.Select(x => x.itemID);

var rows = DbContext.ItemList
      .FilterByItems(ids, groupingField)
      .Where(p => p.flag == 0)
      .ToList();

foreach (var row in rows)
{
      row.flag = 1;
}

And implementation:

public static class FilterExtensions
{
      public static IQueryable<TEntity> FilterByItems<TEntity, TKey>(this IQueryable<TEntity> query,
            IEnumerable<TKey> items, Expression<Func<TEntity, TKey>> entityKey)
      {
            var entityParam = entityKey.Parameters[0];

            if (items is IQueryable queryableItems)
            {
                  var containsLambda = Expression.Lambda<Func<TEntity, bool>>(
                        Expression.Call(typeof(Queryable), nameof(Queryable.Contains),
                              new[] {typeof(TKey)},
                              queryableItems.Expression,
                              entityKey.Body
                        ),
                        entityParam);
                  return query.Where(containsLambda);
            }
            else
            {
                  var containsLambda = Expression.Lambda<Func<TEntity, bool>>(
                        Expression.Call(typeof(Enumerable), nameof(Enumerable.Contains),
                              new[] {typeof(TKey)},
                              Expression.Constant(items),
                              entityKey.Body
                        ),
                        entityParam);
                  return query.Where(containsLambda);
            }
      }

      public static IQueryable<TEntity> FilterByItems<TEntity, TKey>(this IQueryable<TEntity> query,
            IEnumerable<TKey> items, string entityKey)
      {
            var entityParam     = Expression.Parameter(typeof(TEntity), "e");
            var entityKeyLambda =
                  Expression.Lambda<Func<TEntity, TKey>>(Expression.PropertyOrField(entityParam, entityKey), entityParam);

            return query.FilterByItems(items, entityKeyLambda);
      }
}

That works great. Thank you. The only tthing I had to change, was to force the type to be not nullable by adding "?? 0". So it's now: var ids = DbContext.selectedItems.Select(x => x.itemID ?? 0); Otherwise I caught an exception "Expression of type 'System.Int64' cannot be used for return type 'System.Nullable`1[System.Int64]'". — Zeneri, Jun 07 '21 at 14:40

Dynamic LINQ instead of ExecuteSqlRaw

1 Answers1