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
SQL Server Select Top 10-20 Results
In an Oracle Database, Differencebetween Rownum and Row_Number
What Is the Big-O for SQL Select
How to Check If Value Exists in Each Group (After Group By)
SQL Query Selecting Different Row Result in JSON_Modify Because of in Operator Provided Value
How to Group Nearby Latitude and Longitude Locations Stored in SQL
How to Make a Sum Without Group By
Efficient Latest Record Query with Postgresql
Calculating Age from Birthday - Tsql, Oracle, and Any Others
Select Column, If Blank Select from Another
SQL Server Return the Value of Identity Column After Insert Statement
Insert Xml into SQL Server 2008 Database
How to Upsert Multiple Rows with Individual Values in One Statement
Implementing a Total Order Ranking in Postgresql 8.3
How to Update Ms Access Database Table Using Update and Sum() Function