Combining 2 LINQ into one call

Question

Hi, I'm using 2 similar LINQ queries to return a result, the only difference is the where clause (&& s.OptIn == "Yes"). Is there a way to execute this with only one query?

Instead of having a result of

A   2 
B   3

and another result of

A 1
B 1

I want to have

A   2   1 
B   3   1

Here's the LINQ:

        var result = from s in pdc.ScanLogs
                     from e in pdc.Exhibits
                     from ce in pdc.ClientEvents
                     where s.ExhibitID == e.ExhibitID
                     && e.ClientEventID == ce.ClientEventID
                     group 1 by new { ce.EventID } into d
                     select new {
                         EventID = d.Key.EventID,
                         Count = d.Count()
                     };

        var result = from s in pdc.ScanLogs
                     from e in pdc.Exhibits
                     from ce in pdc.ClientEvents
                     where s.ExhibitID == e.ExhibitID
                     && e.ClientEventID == ce.ClientEventID
                     && s.OptIn == "Yes"
                     group 1 by new { ce.EventID } into d
                     select new {
                         EventID = d.Key.EventID,
                         Count = d.Count()
                     };

Answer 1

You can supply a predicate in the Count method. An example is below:

List<int> list = new List<int> { 1, 2, 3, 4, 5 };
var counts = new { CountAll = list.Count(), CountEven = list.Count(i => i % 2 == 0) };
Console.WriteLine(counts.CountEven);

A similar query written for Linq-To-Entities also worked and produced working SQL.

I haven't fully reconstructed your sample, but you should be able to rework it to something like this.

var result = from s in pdc.ScanLogs
                from e in pdc.Exhibits
                from ce in pdc.ClientEvents
                where s.ExhibitID == e.ExhibitID
                && e.ClientEventID == ce.ClientEventID
                group new { s, e, ce } by new { ce.EventID } into d
                select new
                {
                    EventID = d.Key.EventID,
                    Count = d.Count(),
                    CountOptIn = d.Count(item => item.s.OptIn == "Yes")
                }; 

Answer 2

IQueryable<ScanLog> scanlogs = pdc.ScanLogs;
if (filter) scanlogs = scanlogs.Where(...);
var result = from s in scanlogs
   ...