将SQL查询转换为Linq2db

Question

有些人帮助我将SQL查询转换为linq2db联接查询。下面是我的问题。

var query = from a in _accountRepository.Table
                        join b in _documentRepository.Table on a.ID equals b.AccountID
                        join c in _documentTypeRepository.Table on b.DocumentTypeID equals c.ID
                        where a.CompanyID == companyID && b.CompanyID == companyID
                        && c.CompanyID == companyID 
                        && a.IsActive && !a.IsDeleted && c.IsInvoice
                        && b.IsActive && !b.IsDeleted
                        && b.Date.Date >= fromDate.Date && b.Date.Date <=
                            toDate.Date
                        && (b.AccountID == accountID || accountID == null)
                        && (costcenterID.Contains(b.CostCenterID) || costcenterID == null)
                        && a.AccountTypeID == (int)DefaultAccountTypes.Customer
                        group b by new { a.DisplayName, a.ID } into g
                        select new SalesByCustomerModel
                        {
                            AccountID = g.Key.ID,
                            DisplayName = g.Key.DisplayName,
                            InvoiceCount = g.Count(),
                            Sales = g.Sum(x => (x.SubTotal - x.Discount)),
                            SalesWithTax = g.Sum(x => x.Total),
                            Tax = g.Sum(x => x.Tax)
                        };

我得加上这个。我如何才能与linq2db取得成功。

INNER JOIN (            SELECT  ROW_NUMBER() OVER(PARTITION by DocumentID ORDER BY DocumentID) AS SrNo,
                    DocumentID
                FROM DocumentDetail 
                WHERE (DocumentItemID = @itemID OR @itemID IS NULL) 
                    AND CompanyID = @companyID      ) D ON D.DocumentID = B.ID AND SrNo = 1

Answer 1

Linq2db支持窗口函数，我们可以编写详细信息子查询并添加join

var details = 
    from d in _documentDetailRepository.Table
    where (d.DocumentItemID == itemID || itemID == null)
       && d.CompanyID == companyID
    select new 
    {
        d.DocumentID,
        SrNo = Sql.Ext.RowNumber().Over()
            .PartitionBy(d.DocumentID)
            .OrderBy(d.DocumentID)
            .ToValue()
    };

// Then your query can be extended
var query = 
    from a in _accountRepository.Table
    join b in _documentRepository.Table on a.ID equals b.AccountID
    join d in details on new { DocumentID = b.Id, SrNo = 1 } equals new { d.DocumentID, d.SrNo }
    ...