CODEWITHSUNDEEP
c#linqentity-framework
bto.rdz
bto.rdz

Reputation: 6720

Select from multiple tables, map to 1 object

Lets say I Have 3 tables

  1. Purchases
  2. Sales
  3. LostInventory

And simplifing let's define these classes

public class Purchase {
    public int Id {get;set;}
    public int ProductId {get;set;}
    public int PurchasedQ {get;set;}
}
public class Sales{
    public int Id {get;set;}
    public int ProductId {get;set;}
    public int SoldQ {get;set;}
}
public class LostInventory {
    public int Id {get;set;}
    public int ProductId {get;set;}
    public int LostQ {get;set;}
}

time passes and we have 3 purchases, 5 sales and 2 LostInventory objects in our database.

now I would like to let the user know the history of what happened with his products.

for this I was thinking in mapping these objects to a new class called historyItem

public class HistoryItem
{
    public int Q {get; set;}
    public int Type {get;set;} //specifies if the product history comed from a purchase, a sale or a lost
    public int ItemId {get;set;} //specifies the id of the object in its table (according to Type)
}

is my idea ok? if so how could I achivbe this?, I actually cant find a good solution for this, this is what i am trying

var targetProduct = 1; // an example target

IQueryable<ProductHistory> purchasesQuery = db.purchases
           .Where(x => x.ProductId == targetProduct)
           .Select(x => new HistoryItem 
               {
                    Q = x.PurchasedQ,
                    Type = 1,
                    ItemId = x.Id
               });

IQueryable<ProductHistory> salesQuery = db.sales
           .Where(x => x.ProductId == targetProduct)
           .Select(x => new HistoryItem 
               {
                    Q = -x.SoldQ,
                    Type = 2,
                    ItemId = x.Id
               });
IQueryable<ProductHistory> lostQuery = ...

//I need to return an Iqueryable containing them all
return purchasesQuery + salesQuery + lostQuery //how to solve this??

I need to return an IQueryable because it is part of an oData web api controller thanks.

Upvotes: 1

Views: 200

Answers (1)

ycsun
ycsun

Reputation: 1855

How about

purchasesQuery.Concat(salesQuery.Concat(lostQuery))

or

purchasesQuery.Union(salesQuery.Union(lostQuery))

Upvotes: 4

Related Questions