Linq to Sql: 多个左外连接

12 浏览
0 Comments

Linq to Sql: 多个左外连接

我在使用LINQ to SQL时遇到了使用多个左外连接的问题。我知道如何使用一个左外连接。我正在使用VB.NET。以下是我的SQL语法。

T-SQL

SELECT
    o.OrderNumber,
    v.VendorName,
    s.StatusName
FROM
    Orders o
LEFT OUTER JOIN Vendors v ON
    v.Id = o.VendorId
LEFT OUTER JOIN Status s ON
    s.Id = o.StatusId
WHERE
    o.OrderNumber >= 100000 AND
    o.OrderNumber <= 200000

admin 更改状态以发布 2023年5月20日
0

2 答案

0 Comments

没有VisualStudio（我在我的Mac上），但使用http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx中的信息看来你可以做一些像这样的事情：

var query = from o in dc.Orders
            join v in dc.Vendors on o.VendorId equals v.Id into ov
            from x in ov.DefaultIfEmpty()
            join s in dc.Status on o.StatusId equals s.Id into os
            from y in os.DefaultIfEmpty()
            select new { o.OrderNumber, x.VendorName, y.StatusName }

0
0 Comments

这样可能更简洁（您不需要所有的into语句）：

var query = 
    from order in dc.Orders
    from vendor 
    in dc.Vendors
        .Where(v => v.Id == order.VendorId)
        .DefaultIfEmpty()
    from status 
    in dc.Status
        .Where(s => s.Id == order.StatusId)
        .DefaultIfEmpty()
    select new { Order = order, Vendor = vendor, Status = status } 
    //Vendor and Status properties will be null if the left join is null

这是另一个左连接的例子

var results = 
    from expense in expenseDataContext.ExpenseDtos
    where expense.Id == expenseId //some expense id that was passed in
    from category 
    // left join on categories table if exists
    in expenseDataContext.CategoryDtos
                         .Where(c => c.Id == expense.CategoryId)
                         .DefaultIfEmpty() 
    // left join on expense type table if exists
    from expenseType 
    in expenseDataContext.ExpenseTypeDtos
                         .Where(e => e.Id == expense.ExpenseTypeId)
                         .DefaultIfEmpty()
    // left join on currency table if exists
    from currency 
    in expenseDataContext.CurrencyDtos
                         .Where(c => c.CurrencyID == expense.FKCurrencyID)
                         .DefaultIfEmpty() 
    select new 
    { 
        Expense = expense,
        // category will be null if join doesn't exist
        Category = category,
        // expensetype will be null if join doesn't exist
        ExpenseType = expenseType,
        // currency will be null if join doesn't exist
        Currency = currency  
    }

0