EntityFrameworkCore

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
    }
}
```