Entity Framework Core error on temporary table when FromSqlRaw invoques COUNT(*)

Question

I'm using the below query in .NET Core 6. If I use anything other than ToList() I get Incorrect syntax near the keyword 'INTO'. Incorrect syntax near ')'.. This only happens when I use COUNT(*). If anyone can tell me why, I would appreciate.

                using (var context = dbContextFactory.CreateDbContext())
            {
                var query =
                    $"SELECT CONVERT(INT, value) Id INTO #Category FROM STRING_SPLIT(@{CategoryIds}, ',')\n" +
                    $"SELECT CONVERT(INT, value) Id INTO #Location FROM STRING_SPLIT(@{LocationIds}, ',')\n" +
                    $"SELECT\n" +
                    $"  COUNT(DISTINCT FactTenderLot.TenderId) [{nameof(Counter.Value)}]\n" +//FactTenderLot multiplies lines per lots
                    $"FROM\n" +
                    $"  FactTenderLot\n" +
                    $"INNER JOIN\n" +
                    $"  DimTenderStatus\n" +
                    $"ON    FactTenderLot.TenderStatusId = DimTenderStatus.StatusId\n" +
                    $"INNER JOIN\n" +
                    $"  #Location\n" +
                    $"ON    #Location.Id = FactTenderLot.LocationId\n" +
                    $"WHERE\n" +
                    $"  DimTenderStatus.StatusDef <> 4 AND\n" +
                    $"  FactTenderLot.Publicationdate >= @{StartDate} AND\n" +
                    $"  FactTenderLot.Publicationdate <= @{EndDate} AND\n" +
                    $"  EXISTS(SELECT * FROM RelTenderLotCategory WHERE FactTenderLot.TenderId = RelTenderLotCategory.TenderId AND FactTenderLot.LotId = RelTenderLotCategory.LotId AND CategoryId IN(SELECT Id FROM #Category))\n";
                var resultTask = context.CounterSet.FromSqlRaw(query,
                    SqlParameterHelper.Create(StartDate, startDate),
                    SqlParameterHelper.Create(EndDate, endDate),
                    SqlParameterHelper.Create(CategoryIds, string.Join(",", categoryIds)),
                    SqlParameterHelper.Create(LocationIds, string.Join(",", locationIds)));

                var x = await resultTask.ToListAsync(); // It has to be ToList, or else there is a an error in the INTO command.
                return null;

Answer 1

You've got a bug in your string substitutions somewhere. Examine the contents of query and you could find it.

But that pattern of embedding SQL in C# is hard to read and prone to errors. Better something like this, pasting a static SQL query over multiple lines. That way you can actually read the SQL and copy/paste between your C# source and a SQL Query tool:

            var query = $@"
SET NOCOUNT ON;
SELECT CONVERT(INT, value) Id INTO #Category FROM STRING_SPLIT(@CategoryIds, ',')
SELECT CONVERT(INT, value) Id INTO #Location FROM STRING_SPLIT(@LocationIds, ',')

SELECT COUNT(DISTINCT FactTenderLot.TenderId) [Value]//FactTenderLot multiplies lines per lots
FROM FactTenderLot
INNER JOIN DimTenderStatus
ON    FactTenderLot.TenderStatusId = DimTenderStatus.StatusId
INNER JOIN #Location
ON    #Location.Id = FactTenderLot.LocationId
WHERE
    DimTenderStatus.StatusDef <> 4 AND
    FactTenderLot.Publicationdate >= @StartDate AND
    FactTenderLot.Publicationdate <= @EndDate AND
    EXISTS(SELECT * 
           FROM RelTenderLotCategory 
           WHERE FactTenderLot.TenderId = RelTenderLotCategory.TenderId 
           AND FactTenderLot.LotId = RelTenderLotCategory.LotId 
           AND CategoryId IN(SELECT Id FROM #Category))
";
           var resultTask = context.CounterSet.FromSqlRaw(query,
                SqlParameterHelper.Create("@StartDate", startDate),
                SqlParameterHelper.Create("@EndDate", endDate),
                SqlParameterHelper.Create("@CategoryIds", string.Join(",", categoryIds)),
                SqlParameterHelper.Create("@LocationIds", string.Join(",", locationIds)));