I'd like the linq query below to be case sensitive in the Tag == "Storefront" part of the where clause sent the the SQL server database:

  var id = 3203;
  var count = 200000;
  var responseFileTagCountEntity = new ResponseFileTagCountEntity(id);
  var customerId = responseFileTagCountEntity.CustomerId;
  var metaData = new LinqMetaData();
  var pictures = (from t in metaData.ResponseFileTag
                  join f in metaData.ResponseFile on t.ResponseFileId equals f.ResponseFileId
                  join r in metaData.Response on f.ResponseId equals r.ResponseId
                  where t.Tag == "Storefront" // <- make case sensitive
                      && r.CustomerId == customerId
                      && r.DeleteMarkedByUserId == null && r.PublishedByUserId != null
                      && f.DeleteMarkedByUserId == null
                  orderby t.ResponseFileTagId descending
                  select new
                  {
                    f.ResponseFileId,
                    t.Tag,
                    r.CustomerId,
                    r.ResponseBegin2,
                    r.ResponseId,
                    f.FileName
                  })
                  .Take(count)
                  .ToList()
                  .Select(t => new
                  {
                    id = t.ResponseFileId,
                    tag = t.Tag,
                    path = $"{t.CustomerId}/{t.ResponseBegin2.Year}/{t.ResponseBegin2.Month}/{t.ResponseId}/{t.FileName}"
                  });

The linq produces the following SQL:

SELECT TOP(200000) [LPA_L2].[ResponseFileId], [LPA_L1].[Tag], [LPA_L3].[CustomerId], [LPA_L3].[ResponseBegin2], [LPA_L3].[ResponseId], [LPA_L2].[FileName] 
FROM (([InStoreExcellence].[dbo].[ResponseFileTag] [LPA_L1] 
    INNER JOIN [InStoreExcellence].[dbo].[ResponseFile] [LPA_L2] ON [LPA_L1].[ResponseFileId] = [LPA_L2].[ResponseFileId]) 
    INNER JOIN [InStoreExcellence].[dbo].[Response] [LPA_L3] ON [LPA_L2].[ResponseId] = [LPA_L3].[ResponseId]) 
WHERE 
    ( ( ( ( ( ( ( ( ( 
            ( [LPA_L1].[Tag] = 'Storefront') 
        AND ( [LPA_L3].[CustomerId] = 36)) 
        AND ( [LPA_L3].[DeleteMarkedByUserId] IS NULL)) 
        AND ( [LPA_L3].[PublishedByUserId] IS NOT NULL)) 
        AND ( [LPA_L2].[DeleteMarkedByUserId] IS NULL))))))) 
ORDER BY [LPA_L1].[ResponseFileTagId] DESC

Is it possible to add COLLATE SQL_Latin1_General_CP1_CS_AS to the where clause? So the SQL ends up something like this:

SELECT TOP(200000) [LPA_L2].[ResponseFileId], [LPA_L1].[Tag], [LPA_L3].[CustomerId], [LPA_L3].[ResponseBegin2], [LPA_L3].[ResponseId], [LPA_L2].[FileName] 
FROM (([InStoreExcellence].[dbo].[ResponseFileTag] [LPA_L1] 
    INNER JOIN [InStoreExcellence].[dbo].[ResponseFile] [LPA_L2] ON [LPA_L1].[ResponseFileId] = [LPA_L2].[ResponseFileId]) 
    INNER JOIN [InStoreExcellence].[dbo].[Response] [LPA_L3] ON [LPA_L2].[ResponseId] = [LPA_L3].[ResponseId]) 
WHERE 
    ( ( ( ( ( ( ( ( ( 
            ( [LPA_L1].[Tag] = 'Storefront' COLLATE SQL_Latin1_General_CP1_CS_AS) 
        AND ( [LPA_L3].[CustomerId] = 36)) 
        AND ( [LPA_L3].[DeleteMarkedByUserId] IS NULL)) 
        AND ( [LPA_L3].[PublishedByUserId] IS NOT NULL)) 
        AND ( [LPA_L2].[DeleteMarkedByUserId] IS NULL))))))) 
ORDER BY [LPA_L1].[ResponseFileTagId] DESC

With a custom function mapping this should be possible (so you define a function, which does a field = value comparison but also appends the collation). See: https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_functionmappings.htm In this case the function has to return true/false so it will be accepted in a where clause.

Thanks Otis, for your answer.

I'm going with a "stored procedure > TypedView" solution. I my case it's simpler.

/Morten