Home

Parameters disappearing

Hi

I have LinqPad query which when rendered to SQL loses its parameter.

.Where(
q =>((((q.i.AuditBatchId == batchId) & isAuditBatch)
| (q.i.TransferBatchId == batchId) & !isAuditBatch))

When isAuditBatch equals true, it becomes this

WHERE ([t0].[AuditBatchId] = @p0)

Is there any way to force the parameter to stay?

Full code

int batchId = 10190;
bool isAuditBatch = true;

TblProviderInvoices
.Join(TblCases, c => c.CaseId, i => i.CaseId, (i, c) => new {i, c})
.Join(TblPrograms, ic => ic.c.ProgramId, p => p.ProgramId, (ic, p) => new {ic.i, ic.c, p})
.Join(TblProviderTasks, icp => icp.i.TaskId, t => t.TaskId, (icp, t) => new {icp.i, icp.c, icp.p, t})
.Join(TblCompanies, icpt => icpt.t.ProviderId, c => c.CompanyId, (icpt, co) => new {icpt.i, icpt.c, icpt.p, icpt.t, co})
.Where(
q =>((((q.i.AuditBatchId == batchId) & isAuditBatch)
| (q.i.TransferBatchId == batchId) & !isAuditBatch))
& (q.c.NonNationalOperationsCase ?? true)
& (q.t.TaskId >= TblInContactUpgradeDatabaseVariables.FirstOrDefault().GoLiveTaskId )
)
.OrderByDescending(q => q.i.InvoiceId)
.Select(q => new
{
q.p.ProgramName,
q.p.GLCode,
ProviderName = q.co.LegalName,
q.co.CompanyId,
InvoiceNumber = q.i.InvoiceNo,
q.i.InvoiceDate,
q.c.CaseId,
TblProviderTaskStatuses.FirstOrDefault(s => s.JobStatusId ==q.t.JobStatusId).JobStatus,
q.i.InclGSTEstAmnt,
q.i.InclGSTAmnt,
q.i.ModifyUserId,
q.i.ModifyDateTime,
q.i.AuditBatchId
})
.Union(
TblProviderInvoices
.Join(TblCases, c => c.CaseId, i => i.CaseId, (i, c) => new {i, c})
.Join(TblPrograms, ic => ic.c.ProgramId, p => p.ProgramId, (ic, p) => new {ic.i, ic.c, p})
.Join(TblTasks, icp => icp.i.TaskId, t => t.TaskId, (icp, t) => new {icp.i, icp.c, icp.p, t})
.Join(TblCompanies, icpt => icpt.t.Plng1, c => c.CompanyId, (icpt, co) => new {icpt.i, icpt.c, icpt.p, icpt.t, co})
.Where(
q =>((((q.i.AuditBatchId == batchId) & isAuditBatch)
| (q.i.TransferBatchId == batchId) & !isAuditBatch))
& (q.t.TaskType == "Provider")
& (q.c.NonNationalOperationsCase ?? true)
& (q.t.TaskId < TblInContactUpgradeDatabaseVariables.FirstOrDefault().GoLiveTaskId )
)
.OrderByDescending(q => q.i.InvoiceId)
.Select(q => new
{
q.p.ProgramName,
q.p.GLCode,
ProviderName = q.co.LegalName,
q.co.CompanyId,
InvoiceNumber = q.i.InvoiceNo,
q.i.InvoiceDate,
q.c.CaseId,
TblProviderTaskStatuses.FirstOrDefault(s => s.JobStatusId ==q.t.Plng3).JobStatus,
q.i.InclGSTEstAmnt,
q.i.InclGSTAmnt,
q.i.ModifyUserId,
q.i.ModifyDateTime,
q.i.AuditBatchId
})).Dump();


Full SQL

-- Region Parameters
DECLARE @p0 Int = 10190
DECLARE @p1 Int = 1
DECLARE @p2 Int = 10190
DECLARE @p3 NVarChar(1000) = 'Provider'
DECLARE @p4 Int = 1
-- EndRegion
SELECT [t18].[ProgramName], [t18].[GLCode], [t18].[LegalName] AS [ProviderName], [t18].[CompanyId], [t18].[InvoiceNo] AS [InvoiceNumber], [t18].[InvoiceDate], [t18].[CaseId], [t18].[value] AS [JobStatus], [t18].[InclGSTEstAmnt], [t18].[InclGSTAmnt], [t18].[ModifyUserId], [t18].[ModifyDateTime], [t18].[AuditBatchId]
FROM (
SELECT [t2].[ProgramName], [t2].[GLCode], [t4].[LegalName], [t4].[CompanyId], [t0].[InvoiceNo], [t0].[InvoiceDate], [t1].[CaseId], (
SELECT [t8].[JobStatus]
FROM (
SELECT TOP (1) [t7].[JobStatus]
FROM [tblProviderTaskStatus] AS [t7]
WHERE ([t7].[JobStatusId]) = [t3].[JobStatusId]
) AS [t8]
) AS [value], [t0].[InclGSTEstAmnt], [t0].[InclGSTAmnt], [t0].[ModifyUserId], [t0].[ModifyDateTime], [t0].[AuditBatchId]
FROM [tblProviderInvoices] AS [t0]
INNER JOIN [tblCases] AS [t1] ON [t0].[CaseId] = ([t1].[CaseId])
INNER JOIN [tblPrograms] AS [t2] ON [t1].[ProgramId] = ([t2].[ProgramId])
INNER JOIN [tblProviderTask] AS [t3] ON [t0].[TaskId] = [t3].[TaskId]
INNER JOIN [tblCompanies] AS [t4] ON [t3].[ProviderId] = ([t4].[CompanyId])
WHERE ([t0].[AuditBatchId] = @p0) AND ((COALESCE([t1].[NonNationalOperationsCase],@p1)) = 1) AND (([t3].[TaskId]) >= ((
SELECT [t6].[GoLiveTaskId]
FROM (
SELECT TOP (1) [t5].[GoLiveTaskId]
FROM [tblInContactUpgradeDatabaseVariables] AS [t5]
) AS [t6]
)))
UNION
SELECT [t11].[ProgramName], [t11].[GLCode], [t13].[LegalName], [t13].[CompanyId], [t9].[InvoiceNo], [t9].[InvoiceDate], [t10].[CaseId], (
SELECT [t17].[JobStatus]
FROM (
SELECT TOP (1) [t16].[JobStatus]
FROM [tblProviderTaskStatus] AS [t16]
WHERE ([t16].[JobStatusId]) = [t12].[Plng3]
) AS [t17]
) AS [value], [t9].[InclGSTEstAmnt], [t9].[InclGSTAmnt], [t9].[ModifyUserId], [t9].[ModifyDateTime], [t9].[AuditBatchId]
FROM [tblProviderInvoices] AS [t9]
INNER JOIN [tblCases] AS [t10] ON [t9].[CaseId] = ([t10].[CaseId])
INNER JOIN [tblPrograms] AS [t11] ON [t10].[ProgramId] = ([t11].[ProgramId])
INNER JOIN [tblTasks] AS [t12] ON [t9].[TaskId] = [t12].[TaskId]
INNER JOIN [tblCompanies] AS [t13] ON [t12].[Plng1] = ([t13].[CompanyId])
WHERE ([t9].[AuditBatchId] = @p2) AND ([t12].[TaskType] = @p3) AND ((COALESCE([t10].[NonNationalOperationsCase],@p4)) = 1) AND (([t12].[TaskId]) < ((
SELECT [t15].[GoLiveTaskId]
FROM (
SELECT TOP (1) [t14].[GoLiveTaskId]
FROM [tblInContactUpgradeDatabaseVariables] AS [t14]
) AS [t15]
)))
) AS [t18]

Thank you in advance

Comments

  • > Is there any way to force the parameter to stay?

    Just curious, why do you want it to stay?
  • I am writing an SSRS report which has that particular parameter. As SSRS only accepts SQL and I much prefer to write in LINQ, I am using the generated SQL as the datasource. I could manually manipulate the generated SQL but I would like to avoid that at all costs, other than the Parameters Region.

    Cheers
  • First, check whether you use the correct boolean operators (google bitwise versus logical for explanation of difference between & and &&, and | and ||).

    If you want to keep the boolean parameter (keep LINQ-to-SQL from optimizing the expression tree), you can use a compiled query.

    E.g.
    int batchId = 10190;

    var query = CompiledQuery
    .Compile( (UserQuery db, bool isAuditBatch) =>
    db.TblProviderInvoices
    .Where(
    x => x.AuditBatchId == batchId && isAuditBatch
    || x.TransferBatchId == batchId && !isAuditBatch));

    // isAuditBatch == true
    query.Invoke(this, true).Dump();

    // isAuditBatch == false
    query.Invoke(this, false).Dump();
    which results in something like:
    -- Region Parameters
    DECLARE @p0 Int = 10190
    DECLARE @p1 Bit = 1
    DECLARE @p2 Int = 10190
    DECLARE @p3 Bit = 1
    -- EndRegion
    SELECT [t0].*
    FROM [TblProviderInvoices] AS [t0]
    WHERE (([t0].[AuditBatchId] = @p0) AND (@p1 = 1)) OR (([t0].[TransferBatchId] = @p2) AND (NOT (@p3 = 1)))
    GO

    -- Region Parameters
    DECLARE @p0 Int = 10190
    DECLARE @p1 Bit = 0
    DECLARE @p2 Int = 10190
    DECLARE @p3 Bit = 0
    -- EndRegion
    SELECT [t0].*
    FROM [TblProviderInvoices] AS [t0]
    WHERE (([t0].[AuditBatchId] = @p0) AND (@p1 = 1)) OR (([t0].[TransferBatchId] = @p2) AND (NOT (@p3 = 1)))
    Alternatively, you could replace the batchId / isAuditBatch parameters by the parameters auditBatchId / transferBatchId and make sure only one of them is filled (check for NULL explicitly), check if only one of them matches the criteria (as shown by using ^ (XOR)). If this doesn't matter, just replace ^ by ||:
    int? auditBatchId = 10190;
    int? transferBatchId = null;

    TblProviderInvoices
    .Where(
    x => x.AuditBatchId == auditBatchId
    ^ x.TransferBatchId == transferBatchId)
    .Dump();
    Result:
    -- Region Parameters
    DECLARE @p0 Int = 10190
    DECLARE @p1 Int = null
    -- EndRegion
    SELECT *
    FROM [TblProviderInvoices] AS [t0]
    WHERE ((
    (CASE
    WHEN ([t0].[AuditBatchId]) = @p0 THEN 1
    WHEN NOT (([t0].[AuditBatchId]) = @p0) THEN 0
    ELSE NULL
    END)) ^ (
    (CASE
    WHEN [t0].[TransferBatchId] = @p1 THEN 1
    WHEN NOT ([t0].[TransferBatchId] = @p1) THEN 0
    ELSE NULL
    END))) = 1
Sign In or Register to comment.