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.

enter image description here

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)

Is your Purchase mapped bi-directionally? Meaning, a PurchaseItem has a List<Purchase> as a property. If that's the case you can probably simplify it quite a bit. — Pablo Romeo
– Pablo Romeo, Commented Nov 19, 2012 at 22:43
No, it's not currently mapped like that. One approach would perhaps be to do a .ToList() on my first entity and then a for each and populate it entity by entity. Though I'd prefer to have it all queryable as long as possible — Kman
– Kman, Commented Nov 19, 2012 at 22:48
Iterating is probably not a good idea, since you'll be issuing a bunch of queries to the database, when it could be done with one. If you had the bidirectional relationship you wouldn't have to be joining by ids and things like that. There's a few different approaches, i'll see if i can post a few for you to try. Then you can trace SQL to ensure it's only issuing a single query. — Pablo Romeo
– Pablo Romeo, Commented Nov 19, 2012 at 22:51
Yes, you're probably right that iterating is not the best approach. I've started to rewrite the linq from "subquery" to join into's. Perhaps that may be easier. — Kman
– Kman, Commented Nov 19, 2012 at 23:00
I just posted a few different alternatives you can try. I usually try to avoid manual joins, since that's the whole purpose of an ORM, to avoid manual work by having entities have real relationships. — Pablo Romeo
– Pablo Romeo, Commented Nov 19, 2012 at 23:02

Pablo Romeo · Accepted Answer · 2012-11-19 22:57:17Z

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.

Thanks,.. I'll look into both of your examples. Should be able to post the results back tomorrow as I've have had enough of linq as of today :) Thanks again

SBurris · Accepted Answer · 2012-11-19 23:59:53Z

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

Collectives™ on Stack Overflow

Linq query (sql to linq)

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related