LINQ，EF联合查询join

public object GetListAdmin()
{
//return db_C56.Admins
// .Where(a => a.Status != "D").ToList();
var query1 = db_C56.Admins.Join(db_C56.Area, a => a.AreaID, ar => ar.ID, (a, ar) => new
{
userName = a.UserName,
pwd = a.Password,
dName = a.DisplayName,
areaId = a.AreaID,
hasNode = a.HasNode,
roleName = a.RoleName,
status = a.Status,
areaName = ar.Name
});
var query = from a in db_C56.Admins
join ar in db_C56.Area
on a.AreaID equals ar.ID
where a.Status != "D"
select new
{
userName = a.UserName,
pwd = a.Password,
dName = a.DisplayName,
areaId = a.AreaID,
hasNode = a.HasNode,
roleName = a.RoleName,
status = a.Status,
areaName = ar.Name
};
return query.ToList().Select(C => new Admin
{
UserName = C.userName,
Password = C.pwd,
DisplayName = C.dName,
AreaID = C.areaId,
AreaPath = C.areaName,
HasNode = C.hasNode,
RoleName = C.roleName,
Status = C.status,
});
}

[html] view plain copy

from v in Pdt_Versions
join t in Tb_TypeDics
on v.TypeName equals t.TypeName into ignored
from i in ignored.DefaultIfEmpty()
where v.Status != "D"
select new
{
ID = v.ID,
VersionName = v.VersionName,
VersionCode = v.VersionCode,
DownloadName = v.DownloadName,
DownloadURL = v.DownloadURL,
VType = v.VType,
TypeName = v.TypeName,
DisplyTypeName = i.DisplyTypeName,
}

Linq 多层嵌套查询

[csharp] view plain copy

var query1 = from p in dbContent.PostService
where p.post_type == "product" &&
(from ot1 in dbContent.OrderItemmetaService
where
(from ot2 in dbContent.OrderItemsService
where ot2.order_item_type == "line_item" &&
(from p1 in dbContent.PostService
where p1.post_type == "shop_order" && p1.post_author == userid && p1.post_status == "wc-completed"
select p1.ID).Contains(ot2.order_id)
select ot2.order_item_id).Contains(ot1.meta_id)
select ot1.meta_value).Contains(p.ID)
select new
{
id = p.ID,
name = p.post_title
};
var query2 = dbContent.PostService.Where(p =>
p.post_type == "product" &&
(dbContent.OrderItemmetaService.Where(ot1 =>
(dbContent.OrderItemsService.Where(ot2 =>
ot2.order_item_type == "line_item" && (dbContent.PostService.Where(p1 =>
p1.post_type == "shop_order" && p1.post_author == userid && p1.post_status == "wc-completed").Select(p1 => p1.ID).Contains(ot2.order_item_id))
).Select(ot2 => ot2.order_item_id).Contains(ot1.meta_id))
).Select(ot1 => ot1.meta_value).Contains(p.ID))
).Select(p => new
{
id = p.ID,
name = p.post_title
}).ToList();

Left Join 查询

from d in Doctors
join c in (
(from t in Commentaries where t.State != 'D' group t by new { t.DoctorID } into g
select new {
DoctorID = (Int64?)g.Key.DoctorID,
Total = (Int32?)g.Sum(p => p.Rating),
Evaluate = (System.Double?)g.Average(p => p.Rating)
})) on new { UserID = d.UserID } equals new { UserID = (Int64)c.DoctorID }into a_join
from p in a_join.DefaultIfEmpty()
select new {
d.ID,
UserID = (Int64?)d.UserID,
d.Name,
Evaluate = ((int?)p.Evaluate ?? (int?)0)
}

Lambda表达式

Doctors
.GroupJoin (
Commentaries
.Where (t => ((Int32)(t.State) != 68))
.GroupBy (
t =>
new
{
DoctorID = t.DoctorID
}
)
.Select (
g =>
new
{
DoctorID = (Int64?)(g.Key.DoctorID),
Total = (Int32?)(g.Sum (p => p.Rating)),
Evaluate = (Double?)(g.Average (p => p.Rating))
}
),
d =>
new
{
UserID = d.UserID
},
c =>
new
{
UserID = (Int64)(c.DoctorID)
},
(d, a_join) =>
new
{
d = d,
a_join = a_join
}
)
.SelectMany (
temp0 => temp0.a_join.DefaultIfEmpty (),
(temp0, p) =>
new
{
ID = temp0.d.ID,
UserID = (Int64?)(temp0.d.UserID),
Name = temp0.d.Name,
Evaluate = ((Int32?)(p.Evaluate) ?? (Int32?)0)
}
)

======================================================================

多个left join

from d in Doctors
join f in Functions on new { FunctionID = d.FunctionID } equals new { FunctionID = f.ID } into b_join
from f in b_join.DefaultIfEmpty()
join c in (
(from t in Commentaries where t.State != 'D' group t by new {t.DoctorID } into g
select new {
DoctorID = (Int64?)g.Key.DoctorID,
Total = (Int32?)g.Sum(p => p.Rating),
Evaluate = (System.Double?)g.Average(p => p.Rating)
})) on new { UserID = d.UserID } equals new { UserID = (Int64)c.DoctorID } into a_join
from c in a_join.DefaultIfEmpty()
select new {
d.ID,
UserID = (Int64?)d.UserID,
d.AvatarPic,
d.Name,
f.Title,
f.ContentDescribe,
Evaluate = ((int?)c.Evaluate ?? (int?)0)
}

Lambda表达式

Doctors
   .GroupJoin (
Functions,
d =>
   new
   {
FunctionID = d.FunctionID
   },
f =>
   new
   {
FunctionID = f.ID
   },
(d, b_join) =>
   new
   {
d = d,
b_join = b_join
   }
   )
   .SelectMany (
temp0 => temp0.b_join.DefaultIfEmpty (),
(temp0, f) =>
   new
   {
temp0 = temp0,
f = f
   }
   )
   .GroupJoin (
Commentaries
   .Where (t => ((Int32)(t.State) != 68))
   .GroupBy (
t =>
   new
   {
DoctorID = t.DoctorID
   }
   )
   .Select (
g =>
   new
   {
DoctorID = (Int64?)(g.Key.DoctorID),
Total = (Int32?)(g.Sum (p => p.Rating)),
Evaluate = (Double?)(g.Average (p => p.Rating))
   }
   ),
temp1 =>
   new
   {
UserID = temp1.temp0.d.UserID
   },
c =>
   new
   {
UserID = (Int64)(c.DoctorID)
   },
(temp1, a_join) =>
   new
   {
temp1 = temp1,
a_join = a_join
   }
   )
   .SelectMany (
temp2 => temp2.a_join.DefaultIfEmpty (),
(temp2, c) =>
   new
   {
ID = temp2.temp1.temp0.d.ID,
UserID = (Int64?)(temp2.temp1.temp0.d.UserID),
AvatarPic = temp2.temp1.temp0.d.AvatarPic,
Name = temp2.temp1.temp0.d.Name,
Title = temp2.temp1.f.Title,
ContentDescribe = temp2.temp1.f.ContentDescribe,
Evaluate = ((Int32?)(c.Evaluate) ?? (Int32?)0)
   }
   )