ehsanfaridi ehsanfaridi - 18 days ago 5
SQL Question

How can I Join 4 table in Linq query

I use SQL 2014 and vs 2013 and EF4.

I have 4 tables in database:


  • SupplierPurchaseAlarm

  • Order

  • OrderDetail

  • Product



I want join these tables and get
NewOrderCount
,
AllSellProductCount
and
SumOf Price
in
orderDetail
.

I wrote this code but it doesn't work for me:

public ShowAllSupplierAlarmInfo GetAllMsgAndAlarmCount(long suplierId)
{
try
{
var supplierPurchaseList = _db.Context.SuplierPurchaseAlarms.Where(x => x.SuplierId == suplierId).AsQueryable();
var query = from supPurchaseMsg in supplierPurchaseList
join ord in _db.Context.Orders on supPurchaseMsg.OrderId equals ord.OrderID
join ordDetail in _db.Context.OrderDetails on ord.OrderID equals ordDetail.OrderID
join prd in _db.Context.Products on ordDetail.ProductID equals prd.ProductID
where prd.SupplierID==supPurchaseMsg.SuplierId
where
ord.IsPayed && ord.CurentOrderState == (int) EnumCurrentOrderState.ProductIsPreparingToSend ||
ord.CurentOrderState == (int) EnumCurrentOrderState.TheProductIsWaitingForApprovalBySuppliers

group ordDetail by ord
into j
select new
{
OrderCount = j.GroupBy(x => x.Order.OrderID).Count(),
SellProductCount = j.Sum(x => x.Quantity),
AllOrderCount = j.Count(x => x.Order.IsPayed),
profit = j.Sum(x=>x.UnitPrice*x.Quantity)
};
var res = new ShowAllSupplierAlarmInfo()
{
PurchaseAlertCount = supplierPurchaseList.Count(x => x.TypeId==(int)AlarmTypeId.Warning)
};

foreach (var prd in query)
{
res.SellCount += prd.SellProductCount;
res.NewOrderCount += prd.OrderCount;
res.AllOrderCount += prd.AllOrderCount;
res.Profit = prd.profit;
}
return res;
}

Answer

I solved it ...

            var query = _db.Context.Orders.GroupJoin(_db.Context.OrderDetails, ord => ord.OrderID,
                orddetail => orddetail.OrderID, (ord, ordd) => new
                {
                    ord.CurentOrderState,
                    ord.CreationDate,
                    ord.TotalPriceAfterUsingCoupon,
                    ord.OrderID,
                    OrDetail = ordd.Join(_db.Context.Products, x => x.ProductID, x => x.ProductID,
                        (orddeta, prod) => new OrDetail { Productname = prod.ProductName, UnitPrice = orddeta.UnitPrice, Quantity = orddeta.Quantity }
                        )
                }).ToList();

            var result =  query.Select(item => new ShowAllProductInUserOrder()
            {
                OrderId = item.OrderID, CurentOrderState = item.CurentOrderState, CreationDate = item.CreationDate, TotalPriceAfterUsingCoupon = item.TotalPriceAfterUsingCoupon, OrderDetail = item.OrDetail.ToList()
            }).ToList();
            return result;