Parameters disappearing

emmess74

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

sgmoore

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

Just curious, why do you want it to stay?

emmess74

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

nescafe

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