C#•3mo ago

CROSS APPLY within LINQ

Is there a tool I can use to directly map SQL to LINQ? I have a query that I'm not sure how to translate. Here's the SQL:

SELECT [f].[FileName], [a].[ClientName], [f].[FilenameRegex], [f].[Usernames], [f].[RateLimit], [t0].[AssignmentId], [t0].[TimeIssued], [t0].[TimeCompleted], [t0].[Status]
FROM [AssignmentDetails] AS [f]
INNER JOIN [Assignment] as [a] ON [f].[FileName] = [a].[FileName]
CROSS APPLY (SELECT TOP 1 * FROM Assignments WHERE FileName = w.FileName ORDER BY AssignmentId DESC) t0
WHERE [a].[ClientName] = "Apache"

SELECT [f].[FileName], [a].[ClientName], [f].[FilenameRegex], [f].[Usernames], [f].[RateLimit], [t0].[AssignmentId], [t0].[TimeIssued], [t0].[TimeCompleted], [t0].[Status]
FROM [AssignmentDetails] AS [f]
INNER JOIN [Assignment] as [a] ON [f].[FileName] = [a].[FileName]
CROSS APPLY (SELECT TOP 1 * FROM Assignments WHERE FileName = w.FileName ORDER BY AssignmentId DESC) t0
WHERE [a].[ClientName] = "Apache"

I can't figure out how to do CROSS APPLY within LINQ. This was my attempt, but it creates a horrible web of SQL that traverses the entire Assignments table just to find a single record.

var assignmentSummaries = ctx.AssignmentDetails
    .Where(a => a.Assignment.ClientName == command.ClientName)
    .Select(a => new AssigmentDetailsViewModel()
    {
        FileName = a.FileName,
        ClientName = a.Assignment.ClientName,
        AssignmentDetails = new()
        {
            FilenameRegex = a.FilenameRegex,
            RateLimit = a.RateLimit,
            Usernames = a.Usernames,
          
        },
        LatestFile = a.Assignment.Files
            .OrderByDescending(f => f.AssignmentId)
            .Select(f => new AssignmentFile()
            {
                AssignmentId = f.AssignmentId,
                TimeIssued = f.TimeIssued,
                TimeCompleted = f.TimeCompleted,
                Status = f.Status,
            })
            .Take(1)
    })
    .ToList();

var assignmentSummaries = ctx.AssignmentDetails
    .Where(a => a.Assignment.ClientName == command.ClientName)
    .Select(a => new AssigmentDetailsViewModel()
    {
        FileName = a.FileName,
        ClientName = a.Assignment.ClientName,
        AssignmentDetails = new()
        {
            FilenameRegex = a.FilenameRegex,
            RateLimit = a.RateLimit,
            Usernames = a.Usernames,
          
        },
        LatestFile = a.Assignment.Files
            .OrderByDescending(f => f.AssignmentId)
            .Select(f => new AssignmentFile()
            {
                AssignmentId = f.AssignmentId,
                TimeIssued = f.TimeIssued,
                TimeCompleted = f.TimeCompleted,
                Status = f.Status,
            })
            .Take(1)
    })
    .ToList();

The only way we've found that works so far is to not add the LatestFile here, and run a client-side foreach loop after we get the results.

5 Replies

SpReeD•3mo ago

More than 2hrs and no answer, I'm by far no expert but I suggest using LINQ with query-syntax for that purpose. It may clarify things. Also CROSS APPLY is afaik just a multiple SELECT in LINQ, so it might look like this

var query = from a in context.TableA
            from b in context.TableB
                             .Where(x => x.TableA_Id == a.Id)
                             .OrderBy(x => x.Value)
                             .Take(10)
            where a.Key >= 0 && a.Key <= 999
            select new
            {
              a.Key,
              b.Value,
            };

var query = from a in context.TableA
            from b in context.TableB
                             .Where(x => x.TableA_Id == a.Id)
                             .OrderBy(x => x.Value)
                             .Take(10)
            where a.Key >= 0 && a.Key <= 999
            select new
            {
              a.Key,
              b.Value,
            };

Apache•3mo ago

I have seen that Stack Overflow reply. Is there a way with method chaining?

SpReeD•3mo ago

There is, through it's not needed. No matter which syntax you use, either query-syntax or the extenstion method (fluent style). Under the hood it's still the same code. You might wanna take a look at this: https://codeblog.jonskeet.uk/2011/01/28/reimplementing-linq-to-objects-part-41-how-query-expressions-work/

jonskeet

Jon Skeet's coding blog

Reimplementing LINQ to Objects: Part 41 – How query expressions work

Okay, first a quick plug. This won’t be in as much detail as chapter 11 of C# in Depth. If you want more, buy a copy. (Until Feb 1st, there’s 43% off it if you buy it from Manning with …

Apache•3mo ago

I know it's the same code. It's that everywhere in the codebase uses method chaining, and we would like to keep it that way. I've solved it with method chaining by having a separate foreach loop after it's been enumerated, but there should be a way to do it properly. This seems like giving up. I would like to find a tool that can show the SQL above, as LINQ. Similar to how you can change between C#/VB/IL with dnSpy. LIke LinqPad, but in reverse.

D.Mentia•3mo ago

don't think there is such a tool. But you can always work backwards if you need to, sometimes produces a cleaner SQL query in these sorts of cases where EF otherwise struggles to do so ctx.AssignmentFiles.OrderByDescending(...).Where(x => x.Assignment.ClientName == command.ClientName).Select(x => new AssignmentDetailsViewModel { FileName = x.Assignment.AssignmentDetails.FileName, ... }

Gaming

Programming

CROSS APPLY within LINQ