How to iterate over a list to build a Linq query

Question

I have the following working query:

posts.Where(post =>
    post.Fields
        .Where(x =>
            x.RegionId == "RecipeArticleDetails" &&
            (x.FieldId == "RecipePrepTime" || x.FieldId == "RecipeCookTime")
        )
        .GroupBy(x => x.PostId)
        .Select(x => new { ID = x.Key, Value = x.Sum(y => Convert.ToInt32(y.Value)) })
        .Where(x => x.Value > 10 && x.Value < 40)
        .Any()
)
List<string> suppliedTimes = new List<string>(){
    "10-60","0-10"
};

I would like to replace Where(x => x.Value > 10 && x.Value < 40) so it looks up from a list of ranges:

List<string> suppliedTimes = new List<string>(){
    "10-60","0-10"
};

My understanding is I can use select to iterate over the items:

posts.Where(post =>
    suppliedTimes.Select(x => new {low = Convert.ToInt32(x.Split("-",StringSplitOptions.RemoveEmptyEntries)[0]), high = Convert.ToInt32(x.Split("-",StringSplitOptions.RemoveEmptyEntries)[1]) })
    .Any( a =>
        post.Fields
            .Where(x =>
                x.RegionId == "RecipeArticleDetails" &&
                (x.FieldId == "RecipePrepTime" || x.FieldId == "RecipeCookTime")
            )
            .GroupBy(x => x.PostId)
            .Select(x => new { ID = x.Key, Value = x.Sum(y => Convert.ToInt32(y.Value)) })
            .Where(x => x.Value > a.low && x.Value < a.high)
            .Any()
        )
)

However this code results in the error:

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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Please can someone explain how I can achieve this and why what I have isn't working.

Which EF version. It looks like EF Core, but tag is marked as EF 6. — Svyatoslav Danyliv, Feb 07 '23 at 18:17
Anyway EF any versions supports only `Contains` or very simple `Any` with local collections. — Svyatoslav Danyliv, Feb 07 '23 at 18:19
@SvyatoslavDanyliv it's EF Core. Is it possible with this version? — atoms, Feb 07 '23 at 18:28
Everything is possible, but it may need additional efforts. Will prepare sample how to do that. — Svyatoslav Danyliv, Feb 07 '23 at 18:30
@SvyatoslavDanyliv Many thanks! I would be very interested to see how this is done. I've just been reading about supported methods and they seem to be supported: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/supported-and-unsupported-linq-methods-linq-to-entities?redirectedfrom=MSDN&WT.mc_id=email — atoms, Feb 07 '23 at 18:43

score 1 · Accepted Answer · answered Feb 07 '23 at 18:47

To make it work with EF Core I would suggest my extnsion FilterByItems and change the way how to retrieve records.

List<string> suppliedTimes = new List<string>(){
    "10-60","0-10"
};

var ranges = suppliedTimes
    .Select(x => x.Split("-", StringSplitOptions.RemoveEmptyEntries))
    .Select(x => new {
        low = Convert.ToInt32(x[0]), 
        high = Convert.ToInt32(x[1]) 
    });
    
var fields = context.Fields
    .Where(x =>
        x.RegionId == "RecipeArticleDetails" &&
        (x.FieldId == "RecipePrepTime" || x.FieldId == "RecipeCookTime")
    )
    .GroupBy(x => x.PostId)
    .Select(x => new { ID = x.Key, Value = x.Sum(y => Convert.ToInt32(y.Value)) })
    .FilterByItems(ranges, (e, r) => e.Value > r.low && e.Value < r.high, true);

var posts = posts
    .Join(fields, p => p.Id, f => f.ID, (p, f) => p);

Thats worked, sincere thanks! I would have prefered to achieve this without an extension method, but have been trying to figure this out for long enough! Do you know what was causing the select to fail and produce that error? Is it just that what I was trying to achieve is unsupported in EF core? It seems to me like it should have worked. — atoms, Feb 07 '23 at 19:00
Answer is obvious, EF Core supports ONLY `Contains` with local collection. I have created such extension method to solve repetitive questions on SO. It produces effective SQL and reduces amount of code needed for such operations. — Svyatoslav Danyliv, Feb 07 '23 at 19:03

How to iterate over a list to build a Linq query

1 Answers1