Convert SQL query to Linq2db

Question

Some one help me to convert SQL query to linq2db join query. Below is my query.

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)
                        };

I have to add this. How can I achive with 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

Generally, OR mapping layers are slower (and sometimes MUCH slower) than doing direct SQL. There are semantic gaps/differences with these layers and sometimes they generate SQL that is more verbose and not always possible to optimize. Please explain why you want to go in this direction - it implies that something may be off in your design — Conor Cunningham MSFT, Jul 11 '22 at 12:40
@ConorCunninghamMSFT, if you experienced bad query generation via `linq2db`, post it here. Usually it is well optimised queries with high mapping speed. — Svyatoslav Danyliv, Jul 12 '22 at 16:27

score 0 · Answer 1 · answered Jul 11 '22 at 13:06

Linq2db supports window functions, and we can write details subquery and add 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 }
    ...

I already done with same code. – ReqSudio User Jul 13 '22 at 05:26 — ReqSudio User, Jul 13 '22 at 05:26

Convert SQL query to Linq2db

1 Answers1