CODEWITHSUNDEEP
c#linqentity-framework-core
atoms
atoms

Reputation: 3093

How to iterate over a list to build a Linq query

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.

Upvotes: 1

Views: 99

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

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);

Upvotes: 1

Related Questions