Linq query (sql to linq)

Question

Having three tables (Item, Purchase, PurchaseItem) I am trying to write a linq query which finds how many valid purchases an item is included in.

In standard SQL I would use the subquery with a count, but I can't seem to get a linq query to do this.

SQL:

select i.name, 
InTrade = (select count(*) from Purchase p join PurchaseItem pi on p.id = pi.PurchaseId where p.Isvalid = 1 and pi.ItemId = i.Id)
from Item i

Linq:

from i in Items select new 
{
    i.Name,
    InTrade = from pi in PurchaseItems where pi.ItemId == i.ID
        select new 
            {
                pi,
                Purchase = from p in Purchases where p.ID == pi.PurchaseId
                select new { p }
            }
}

Here I am trying to retrieve all my data in a query, and I would perhaps need another query to actually get what I want. I am probably headed down a troublesome road with this approach, so perhaps someone could shed some light on the "best practice" of achieving this in linq.

I've prepared some test data for this example:

if exists (select * from sysobjects where name = 'PurchaseItem') drop table PurchaseItem
if exists (select * from sysobjects where name = 'Item') drop table Item
if exists (select * from sysobjects where name = 'Purchase') drop table Purchase
go

create table Item
(
    ID int not null primary key,
    Name nvarchar(30) not null
)

create table Purchase
(
    ID int not null primary key,    
    Name nvarchar(30) not null,
    ConfirmedDate datetime,
    Isvalid bit not null
)

create table PurchaseItem
(
    ID int not null primary key,
    PurchaseId int null references Purchase(ID),
    ItemId int null references Item(ID)
)

insert Item values (1, 'Bread')
insert Item values (2, 'Beer')
insert Item values (3, 'Wine')
insert Item values (4, 'Milk')
insert Item values (5, 'Apple')
insert Item values (6, 'Steak')
insert Item values (7, 'Hamburger')
insert Item values (8, 'Vinegar')

insert Purchase values (1, 'Purchase 1', null, 1)
insert Purchase values (2, 'Purchase 2', null, 0)
insert Purchase values (3, 'Purchase 3', '2008-1-7', 1)

insert PurchaseItem values (1, 1, 1)
insert PurchaseItem values (2, 1, 2)
insert PurchaseItem values (3, 1, 3)
insert PurchaseItem values (4, 1, 4)
insert PurchaseItem values (5, 1, 5)
insert PurchaseItem values (6, 1, 6)
insert PurchaseItem values (7, 2, 2)
insert PurchaseItem values (8, 3, 2)
insert PurchaseItem values (9, 3, 1)
insert PurchaseItem values (10, 3, 5)

Answer 1

You can also do this:

var results = from i in db.Items
              select new InTradeResult
              {
                ProductName = i.Name,
                InTradeCount = i.PurchaseItems.Count(pi => pi.Purchase.IsValid)
              };

Here is a sample Console application that shows the results:

class Program
{
  static void Main(string[] args)
  {
    using (var db = new Context())
    {
      var results = from i in db.Items
                    select new InTradeResult
                    {
                      ProductName = i.Name,
                      InTradeCount = i.PurchaseItems.Count(pi => pi.Purchase.IsValid)
                    };

      foreach (var result in results)
      {
        System.Console.WriteLine("{0} - {1}", result.InTrade, result.ProductName);
      }

      System.Console.ReadKey();
    }
  }
}

public class InTradeResult
{
  public string ProductName { get; set; }
  public int InTradeCount { get; set; }
}

public class Context : IDisposable
{
  public IList<Item> Items { get; set; }
  public IList<Purchase> Purchases { get; set; }
  public IList<PurchaseItem> PurchaseItems { get; set; }

  public Context()
  {
    Items = new List<Item>();
    Items.Add(new Item { ID = 1, Name = "Bread" });
    Items.Add(new Item { ID = 2, Name = "Beer" });
    Items.Add(new Item { ID = 3, Name = "Wine" });
    Items.Add(new Item { ID = 4, Name = "Milk" });
    Items.Add(new Item { ID = 5, Name = "Apple" });
    Items.Add(new Item { ID = 6, Name = "Steak" });
    Items.Add(new Item { ID = 7, Name = "Hamburger" });
    Items.Add(new Item { ID = 8, Name = "Vinegar" });

    Purchases = new List<Purchase>();
    Purchases.Add(new Purchase { ID = 1, Name = "Purchase 1", ConfirmedDate = null, IsValid = true });
    Purchases.Add(new Purchase { ID = 2, Name = "Purchase 2", ConfirmedDate = null, IsValid = false });
    Purchases.Add(new Purchase { ID = 3, Name = "Purchase 3", ConfirmedDate = null, IsValid = true });

    PurchaseItems = new List<PurchaseItem>();
    PurchaseItems.Add(new PurchaseItem(1, Purchases[0], Items[0]));
    PurchaseItems.Add(new PurchaseItem(2, Purchases[0], Items[1]));
    PurchaseItems.Add(new PurchaseItem(3, Purchases[0], Items[2]));
    PurchaseItems.Add(new PurchaseItem(4, Purchases[0], Items[3]));
    PurchaseItems.Add(new PurchaseItem(5, Purchases[0], Items[4]));
    PurchaseItems.Add(new PurchaseItem(6, Purchases[0], Items[5]));
    PurchaseItems.Add(new PurchaseItem(7, Purchases[1], Items[1]));
    PurchaseItems.Add(new PurchaseItem(8, Purchases[2], Items[1]));
    PurchaseItems.Add(new PurchaseItem(9, Purchases[2], Items[0]));
    PurchaseItems.Add(new PurchaseItem(10, Purchases[2], Items[4]));
  }

  public void Dispose()
  {
    //Do nothing
  }
}


public class Item
{
  public int ID { get; set; }
  public string Name { get; set; }

  public IList<PurchaseItem> PurchaseItems { get; set; }

  public Item()
  {
    PurchaseItems = new List<PurchaseItem>();
  }
}

public class Purchase
{
  public int ID { get; set; }
  public string Name { get; set; }
  public DateTime? ConfirmedDate { get; set; }
  public bool IsValid { get; set; }

  public IList<PurchaseItem> PurchaseItems { get; set; }

  public Purchase()
  {
    PurchaseItems = new List<PurchaseItem>();
  }
}

public class PurchaseItem
{
  public int ID { get; set; }

  public int PurchaseId { get; set; }
  public Purchase Purchase { get; set; }

  public int ItemId { get; set; }
  public Item Item { get; set; }

  public PurchaseItem(int id, Purchase purchase, Item item)
  {
    ID = id;

    PurchaseId = purchase.ID;
    Purchase = purchase;
    purchase.PurchaseItems.Add(this);

    ItemId = item.ID;
    Item = item;
    item.PurchaseItems.Add(this);
  }
}

Answer 2

There are several ways I think you can accomplish that, through a single query to the database. Here are two I can think of:

Using a bi-directional association between items and their purchases:

var resultUsingSubItems = context.PurchaseItems.Select(x => new
    {
        Item = x,
        ValidPurchases = x.Purchases.Count(y => y.IsValid)
    });

And another approach, grouping by Item:

var resultUsingGrouping = context.Purchases.GroupBy(x => x.Item).Select(
        g => new
            {
                Item = g.Key,
                ValidPurchases = g.Count(y => y.IsValid)
            });

Please use SQL profiler to ensure that Linq2SQL is transforming that into a single SQL query.