left join
var query = from log in logRepository.Table.Include(a => a.User)
join
user in logRepository.Table on log.UserID equals user.ID into lu
from lu1 in lu.DefaultIfEmpty()
select new { log.Title,log.Description,log.User.Name}; 生成的sql
SELECT [log].[ID], [log].[Name], [log].[CompanyName], [log.User].[Password]
FROM [Log] AS [log]
LEFT JOIN [User] AS [log.User] ON [log].[UserID] = [log.User].[ID]
LEFT JOIN [User] AS [user] ON [log].[UserID] = [user].[ID] log.UserID 为可空类型，否则生成的sql中会是inner jion
延迟加载
建议不要使用
每次用到延迟加载属性，都会查一次数据库；
比如列表中(100行)访问延迟加载属性，会多查100次数据库；
比如序列化的时候，也会根据延迟加载属性查询数据库；
# EF示例
```c#
using X.Data;
public class LogService : ILogService
{
IRepository<Log> logRepository;
public LogService(IRepository<Log> logRepository)
{
this.logRepository = logRepository;
}
public void EFSample()
{
//insert
var log = new Log() { Title = "title_insert", Description = "des_insert" };
logRepository.Insert(log);
//update
var log = logRepository.Get("id");
log.Title = "title_udpate";
log.Description = "des_update";
logRepository.Update(log);
//delete
var log = logRepository.Get("id");
logRepository.Delete(log);
//select(single table)
logRepository.Get("id");
logRepository.Get(o=>o.XXX="XXX"); //查询结果唯一，查询条件一般是主键或唯一键
logRepository.Find(o=>o.XXX="XXX");
logRepository.Where(o => o.Title == "title" && o.Description == "des");
logRepository.WhereIf(!title.IsNullOrWriteSpace(), o => o.Title == "title");
//select(join)
// .Include(a => a.User)
// into lu
// from lu1 in lu.DefaultIfEmpty()
var query = from log in logRepository.Include(a => a.User)
join user in logRepository on log.UserID equals user.ID into lu
from lu1 in lu.DefaultIfEmpty()
select new { log.Title, log.Description, log.User.Name };
//inner join
var query = from project in owlDbContext.Project.Include(o=>o.User)
join user in owlDbContext.User on project.UserId equals user.Id
select project;
//left join
var query1 = from project in owlDbContext.Project.Include(o=>o.User)
join user in owlDbContext.User on project.UserId equals user.Id into u from user in u.DefaultIfEmpty()
select project;
var query2 = from project in owlDbContext.Project.Include(o => o.User)
from user in owlDbContext.User.Where(user=> project.UserId==user.Id).DefaultIfEmpty()
select project;
//查询出导航属性：如上 query.Include(o=>o.User)
/*
SELECT[log].[ID], [log].[Name], [log].[CompanyName], [log.User].[Password]
FROM[Log] AS[log]
LEFT JOIN[User] AS[log.User] ON[log].[UserID] = [log.User].[ID]
LEFT JOIN[User] AS[user] ON[log].[UserID] = [user].[ID]
*/
var query = from col in dbColumnRepository
join table in dbTableRepository on col.TableName equals table.TableName
where col.DatabaseName == table.DatabaseName && table.ID == table_id
select col;
其他例子1：
var query1 = from rp in _recurringPaymentRepository.Table
join o in _orderRepository.Table on rp.InitialOrderId equals o.Id
join c in _customerRepository.Table on o.CustomerId equals c.Id
where
!rp.Deleted &&
(showHidden || !o.Deleted) &&
(showHidden || !c.Deleted) &&
(showHidden || rp.IsActive) &&
(customerId == 0 || o.CustomerId == customerId) &&
(storeId == 0 || o.StoreId == storeId) &&
(initialOrderId == 0 || o.Id == initialOrderId) &&
(!initialOrderStatusId.HasValue || initialOrderStatusId.Value == 0 ||
o.OrderStatusId == initialOrderStatusId.Value)
select rp.Id;
var query2 = from rp in _recurringPaymentRepository.Table
where query1.Contains(rp.Id)
orderby rp.StartDateUtc, rp.Id
select rp;
var recurringPayments = new PagedList<RecurringPayment>(query2, pageIndex, pageSize);
return recurringPayments;
其他例子2：
query = from o in query
join oi in _orderItemRepository.Table on o.Id equals oi.OrderId
join p in _productRepository.Table on oi.ProductId equals p.Id
join pwi in _productWarehouseInventoryRepository.Table on p.Id equals pwi.ProductId
where
//"Use multiple warehouses" enabled
//we search in each warehouse
(p.ManageInventoryMethodId == manageStockInventoryMethodId && p.UseMultipleWarehouses && pwi.WarehouseId == warehouseId) ||
//"Use multiple warehouses" disabled
//we use standard "warehouse" property
((p.ManageInventoryMethodId != manageStockInventoryMethodId || !p.UseMultipleWarehouses) && p.WarehouseId == warehouseId)
select o;
其他例子3：(聚合)
var query1 = from c in _customerRepository.Table
join o in _orderRepository.Table on c.Id equals o.CustomerId
where (!createdFromUtc.HasValue || createdFromUtc.Value <= o.CreatedOnUtc) &&
!o.Deleted && !c.Deleted
select new { c, o };
var query2 = from co in query1
group co by co.c.Id into g
select new
{
CustomerId = g.Key,
OrderTotal = g.Sum(x => x.o.OrderTotal),
OrderCount = g.Count()
};
query2 = orderBy switch
{
1 => query2.OrderByDescending(x => x.OrderTotal),
2 => query2.OrderByDescending(x => x.OrderCount),
_ => throw new ArgumentException("Wrong orderBy parameter", nameof(orderBy)),
};
var tmp = new PagedList<dynamic>(query2, pageIndex, pageSize);
return new PagedList<BestCustomerReportLine>(tmp.Select(x => new BestCustomerReportLine
{
CustomerId = x.CustomerId,
OrderTotal = x.OrderTotal,
OrderCount = x.OrderCount
}),tmp.PageIndex, tmp.PageSize, tmp.TotalCount);
其他例子4：
var count =
(from e_ in db.article_type
join a_a in
(from a in db.article_Info
from e in db.article_type
where a.SeventSortID == e.ID
group e by e.Name into g
select new
{
g.Key,
Count_ = g.Count()
}) on e_.Name equals a_a.Key
select new
{
ID = e_.ID,
Name = e_.Name,
count = a_a.Count_
}).ToList();
SELECT C0.ID,C_C.Name,C_C.C_COUNT FROM article_type C0
INNER JOIN(SELECT C.Name,COUNT(C.Name) AS C_COUNT FROM article_type C
INNER JOIN article_Info A ON C.ID = A.SeventSortID GROUP BY C.Name) C_C ON C0.Name = C_C.Name
//更多复杂查询见：https://docs.microsoft.com/zh-cn/ef/core/querying/complex-query-operators
}
}
``` 鹏愿科技
|
企业家
企业头条
资本商学院
商业百科