Join Subquery Result in Linq

Join Subquery result in Linq

Yes, you can join sub queries. Like this:

var query = from f in db.ApplicationFees
join sub in (from p in db.Payments
where p.Type == 1 && p.Position == 1 &&
p.Date >= fromDate && p.Date <= toDate
group p by p.ApplicationNo into g
select new {
ApplicationNo = g.Key,
CNT = g.Count(),
AMNT = g.Sum(x => x.Amount)
})
on f.ApplicationNo equals sub.ApplicationNo into feePayments
select new { Fee = f, Payments = feePayments };

But writing it in single query is not very maintainable. Consider to compose your query from sub-queries defined separately:

var payments = from p in db.Payments
where p.Type == 1 && p.Position == 1 &&
p.Date >= fromDate && p.Date <= toDate
group p by p.ApplicationNo into g
select new {
ApplicationNo = g.Key,
CNT = g.Count(),
AMNT = g.Sum(x => x.Amount)
};

var query = from f in db.ApplicationFees
join p in payments
on f.ApplicationNo equals p.ApplicationNo into feePayments
select new { Fee = f, Payments = feePayments };

How to JOIN a Sub-Query using LINQ

You can do it the exact same way as in SQL. _db.ProductionOrders in LINQ to Entities represents the dbo.ProductionOrders in SQL, and you can use it in both top and subquery level:

var query = _db.ProductionOrders
.Join(_db.ProductionOrders
.GroupBy(p2 => new { p2.OrderNumber })
.Select(g => new { g.Key.OrderNumber, Timestamp = g.Max(e => e.Timestamp) }), // subquery
p1 => new { p1.OrderNumber, p1.Timestamp }, p2 => p2, // join condition
(p1, p2) => p1) // result selector (p1.*)
.Where(p1 => p1.Material == 42);

(Note: Not sure where Label is coming from in your SQL queries, so using OrderNumber instead).

or if you prefer, the same in LINQ query syntax:

var query =
from p1 in _db.ProductionOrders
join p2 in (from p2 in _db.ProductionOrders
group p2 by new { p2.OrderNumber }) into g
select new { g.Key.OrderNumber, Timestamp = g.Max(p2 => p2.Timestamp) })
on new { p1.OrderNumber, p1.Timestamp } equals p2
where p1.Material == 42
select p1;

Why can't I select data from my LINQ sub query join?

You can use GroupJoin with SelectMany for LEFT JOIN SQL Query and get the desired output.

    var result = db.Items.GroupJoin(db.Carts.Where(x => x.CartID == 1), item => item.ItemID, cart => cart.ItemID, 
(item, cart) => new { item, cart })
.SelectMany(x => x.cart.DefaultIfEmpty(), (it, ca) =>
{
return new ItemViewModel
{
ItemName = it.item.ItemName,
Price = it.item.Price,
ItemID = it.item.ItemID,
// ... .... ....
// Fill the required columns from it.Item property..
Qty = ca != null ? ca.Qty : 0
};
}).ToList();

EDIT: The LINQ version with SelectMany.

var result = from s in db.Items
join sub in (from c in db.Carts
where c.CartID == 1
select c)
on s.ItemID equals sub.ItemID into joined
from row in joined.DefaultIfEmpty()
select new ItemViewModel
{
CategoryID = s.CategoryID,
Description = s.Description,
Price = s.Price,
Qty = row != null ? row.Qty : 0,
ItemID = s.ItemID,
ItemName = s.ItemName
};

The C# Fiddle with sample data.

LINQ query with sub-query on LEFT JOIN conditions

after some research I finally found how to do it. Here is the LINQ query that generates the SQL I was trying to get:

var leaseList = (from l in leases.tblfLeaseDetails
join p in leases.tblfPayments
on l.Lease_Detail_ID equals p.Lease_Detail_ID into lp
from jlp in lp.Where(x => x.Payment_ID == (from pj in leases.tblfPayments
where pj.Lease_Detail_ID == l.Lease_Detail_ID
orderby pj.Payment_Date ascending
select pj.Payment_ID).FirstOrDefault()).DefaultIfEmpty()
join a in leases.tblfAuthorizations on l.Lease_Detail_ID equals a.Lease_Detail_ID into la
from jla in la.Where(x => x.Authorization_ID == (from aj in leases.tblfAuthorizations
where aj.Lease_Detail_ID == l.Lease_Detail_ID
orderby aj.Authorized_Date ascending
select aj.Authorization_ID).FirstOrDefault()).DefaultIfEmpty()
join v in leases.tblvVendors on l.Vendor_ID equals v.Vendor_ID into lv
from jlv in lv.DefaultIfEmpty()
join c in leases.tblvCounties on l.County_ID equals c.County_ID into lc
from jlc in lc.DefaultIfEmpty()
select new LeaseViewModel()
{
Lease_Detail_ID = l.Lease_Detail_ID,
Lease_ID = l.Lease_ID,
XRef_Lease_ID = l.XRef_Lease_ID,
Vendor_Name = jlv.Vendor_Name,
Description = l.Description,
County = jlc.County,
Amount = l.Amount,
Payment_Due_Date = l.Payment_Due_Date,
Lease_Type = l.Lease_Type.ToString(),
Location_ID = l.Location_ID,
Active = l.Active,
Expiration_Date = l.Expiration_Date,
Authorized = jla.Authorized,
Payment_Date = jlp.Payment_Date
});

Linq with Left Join on SubQuery containing Count

Not tested, but this should do the trick:

var q = from c in ctx.Category
join clg in
(
from cl in ctx.CategoryListing
group cl by cl.CategoryID into g
select new { CategoryID = g.Key, Frequency = g.Count()}
) on c.CategoryID equals clg.CategoryID into cclg
from v in cclg.DefaultIfEmpty()
where c.GuideID==1
select new { c.CategoryID, Frequency = v.Frequency ?? 0 };

linq inner join sub query and conditional select

you can use Lambda expressions group by and for conditional select use inline condition like below:

var query = (from u in UsageLogs
join c in (UsageLogs.GroupBy(r => r.username).Select(r => new {username = r.Key, lastStart = r.Max(p => p.start)))
on u.username equals c.username
orderby u.lastname,u.firstname,u.program.u.start
select new
{
lastname = u.lastname,
firstname = u.firstname,
program = u.program,
start = u.start,
end = u.end,
error = (u.end == NULL && u.start < c.lastStart) ? 1 : 0,
loggedOn = (u.end == NULL && u.start == c.lastStart) ? 1 : 0,
});

Linq query joining with a subquery

I am using Northwnd sample database
Customers,Orders,Employees table
Here I am getting top 1 order group by customer and order's employeeid
Please let me know If this is matching with your requirement or not

var ord = from o in NDC.Orders
orderby o.OrderID descending
group o by o.CustomerID into g
select new {CustomerID=g.Key,Order=g.OrderByDescending(s=>s.OrderID).First() };

var res1 = from o in ord
join emp in NDC.Employees
on o.Order.EmployeeID equals emp.EmployeeID into oemp
select new {Order=o.Order,employee=oemp };

Response.Write(res1.ToList().Count);
foreach (var order in res1)
{
Response.Write(order.Order.CustomerID + "," +
order.Order.OrderID + ","+
order.Order.EmployeeID+"<br/>");
}

// Above code is working .I have tried to convert your query to linq and replace your datacontext name with 'NDC'

var ord = from rr in NDC.ReqRoutings
orderby rr.ReqRoutingID descending
group rr by rr.ReqID into g
select new
{
ReqID = g.Key,
ReqRoutings = g.OrderByDescending(s => s.ReqRoutingID).First()
};

var res1 = from o in ord
join emp in NDC.ReqRoutingSections on o.ReqRoutings.RoutingSectionID
equals emp.RoutingSectionID into oemp
select new { ReqRoutings = o.ReqRoutings, employee = oemp };

Response.Write(res1.ToList().Count);
foreach (var order in res1)
{
Response.Write(order.ReqRoutings.ReqID + "," +
order.ReqRoutings.ReqRoutingID + "," +
order.ReqRoutings.RoutingSectionID + "<br/>");
}

Please let know if it is help you or not



Related Topics



Leave a reply



Submit