CODEWITHSUNDEEP
c#linq
DarthVegan
DarthVegan

Reputation: 1269

Using Linq to find all rows that match these conditions

var mEstList = new List<Estimate>();
var mDatesList = new List<DateTime>("2020-03-01", "2020-03-02", "2020-03-03");
var namesList = new List<string>("Name1", "Name2", "Name3");

Estimates Table Example:
ID     Name     Estimate     Date
1      Name1    50           2020-03-01
2      Name1    55           2020-03-02
3      Name1    54           2020-03-03
4      Name2    43           2020-03-03
5      Name3    43           2020-03-01
6      Name3    42           2020-03-02
7      Name3    44           2020-03-03

This is what I attempted to do without using linq but I'm trying to figure out how to do the same thing with a single linq command. I'm trying to only return the estimates that match all dates with the master date list from above so in my example I would return all rows except for row #4

      for (int h = 0; h < namesList.Count; h++)
      {
          string name = namesList.ElementAt(h);

          var estList = context.Estimates.Where(x => x.Name == name && x.Date >= 2020-03-01);

          if (estList.Count > 0)
          {
               var finalDate = estList.Max(x => x.Date);
               var sDateList = mDatesList.Where(x => x.Date <= finalDate);

               if (estList.Count == sDateList.Count)
               {
                    mEstList.AddRange(estList);
               }
          }
     }

UPDATE: This is the code I have so far which looks correct but I'm getting the Date is not supported in Linq to Entities exception even though I convert the date in my linq code. I'm stuck so far. Any suggestions?

var mEstList = await context.Estimates.GroupBy(e => e.Name).Where(g => mDatesList.Where(x => x.Date <= DbFunctions.TruncateTime(g.Max(n => n.Date))).
All(d => g.Select(e => e.Date).Contains(d))).SelectMany(g => g).ToListAsync().ConfigureAwait(false);

Upvotes: 2

Views: 636

Answers (1)

Federico Rossi
Federico Rossi

Reputation: 413

I would do something like :

mEstList = context.Estimates.GroupBy(e => e.Name).Where(g => mDatesList.All(d => g.values.Select(e => e.Date).Contains(d))).SelectMany(g => g.values).ToList();

I did not test this, so expect some errors. I hope to give you an idea to solve this problem.

Good luck.

Upvotes: 1

Related Questions