CODEWITHSUNDEEP
c#linq
SpiderKing88
SpiderKing88

Reputation: 547

Easier(better) way to write LINQ query

I have two LINQ queries, I use the results of the 1st LINQ query and use a foreach loop to iterate through each element found and use it in the 2nd LINQ query

var entries = dbo.OnDemand.Where(c => c.LOTNO == LotNo).Where(c => c.ODHisDate >= start && c.ODHisDate <= end).Where(c => c.Activity.Contains(reprinted)).OrderBy(c => c.ODHisDate);

foreach (var X in entries)
{  
    Barcodes.Add(X.Barcode);    // Barcodes is a List<string>
}

foreach (string barcode in Barcodes)
{
    var entries2 = dbo.OnDemand.Where(c => c.Barcode.Contains(barcode)).Where(c => c.ODHisDate >= start && c.ODHisDate <= end).OrderBy(c => c.ODHisDate);

    // Do stuff with entries2
}

Is there a way I could execute this using a single query?

I read around, and people use JOIN but I am not entirely sure how to use it.

Some of the expressions are common for both queries (the selected date range, and the ORDERBY by date)

Currently this way works but I think there is a better way of doing what I am trying to achieve.

Any help would be appreciated.

EDIT 1:

Sample SQL query:

select * FROM [dbo].[OnDemand]
where barcode in
(
SELECT [Barcode]      
  FROM [dbo].[OnDemand]
  group by barcode
  having count(*) > 1
  )  
 and odhisdate between  '2017-01-11' and '2017-01-31' 

 and Activity like '%reprinted%'

  order by ODHisDate asc, ODHisTime asc

Upvotes: 3

Views: 393

Answers (2)

ocuenca
ocuenca

Reputation: 39326

I guess you're doing this because there are elements with the same barcode. A solution could be:

var entries = dbo.OnDemand.Where(c => c.LOTNO == LotNo 
                                   && c.ODHisDate >= start && c.ODHisDate <= end 
                                   && c.Activity.Contains(reprinted))
                          .Select(c => c.Barcode);//Don't need to orderby here

// You will get a IEnumerable<List<OnDemand>> as result of this query
var entries2 =entries.Select(barcode =>  dbo.OnDemand.Where(c => c.Barcode.Contains(barcode) 
                                                              && c.ODHisDate >= start && c.ODHisDate <= end)
                                                     .OrderBy(c => c.ODHisDate)
                                                     .ToList());

foreach (var list in entries2)
{
 // do stuff
}

Better Idea could be filter first by the common conditions, later group by barcode and then keep those groups who have an element that meets the second where conditions

var entries = dbo.OnDemand.Where(c.ODHisDate >= start && c.ODHisDate <= end)
                          .GroupBy(c=>c.BarCode)
                          .Where(g=>g.Any(c=>c.LOTNO == LotNo && c.Activity.Contains(reprinted) ))
                          .Select(g=>g.OrderBy(c => c.ODHisDate).ToList());

Update

var entries = dbo.OnDemand.Where(c.ODHisDate >= start && c.ODHisDate <= end && c.Activity.Contains(reprinted))
                          .GroupBy(c=>c.BarCode)
                          .Where(g=>g.Count()>1)
                          .Select(g=>g.OrderBy(c => c.ODHisDate));

Upvotes: 2

Renats Stozkovs
Renats Stozkovs

Reputation: 2605

As far as I can tell, there is no need for a JOIN because you are querying the same table and you are not trying to do a recursive or cross-join query.

Why do you query the same table with the same parameters? You get a List of entries back, then iterate through Barcode values and then go back again and query the same table with the same Barcode value.

If you want to iterate in-line, the syntax is straight forward, but I am not sure if you understand what you are doing there.

dbo.OnDemand.Where(c => c.Barcode.Contains(barcode))
    .Where(c => c.ODHisDate >= start && c.ODHisDate <= end)
    .OrderBy(c => c.ODHisDate)
    .ForEach(c =>
        {
            // do stuff
        }

Upvotes: 4

Related Questions