C#•2y 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"

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

CROSS APPLY

CROSS APPLY

CROSS APPLY within LINQ.

This was my attempt, but it creates a horrible web of SQL that traverses the entire

Assignments

Assignments

Assignments

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();

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

LatestFile

LatestFile

LatestFile here, and run a client-side

foreach

foreach

loop after we get the results.

SpReeD•3/29/24, 4:55 PM

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

CROSS APPLY

is afaik just a multiple

SELECT

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,
            };

ApacheOP•3/29/24, 5:17 PM

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

SpReeD•3/29/24, 6:08 PM

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/

Jon Skeet's coding blogjonskeet

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 …

ApacheOP•3/29/24, 6:56 PM

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•3/30/24, 1:11 AM

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, ... }

CROSS APPLY within LINQ

Similar Threads

Similar Threads

Similar Threads