How to make linq query for grouped list based on a join condition of two table more efficient

Question

I'm working with Entity Framework Core. I'm trying to write a linq query which takes inputs from two tables SccmTable and Sailpoint table.

I want to satisfy these 3 conditions:

1. lcstate = deleted in sailpoint table
2. UserName in sailpoint equals Username in sccm table
3. Select only rows with unique machine name

I tried this method

var result = (from r in DBContext.SccmTable
              join s in DBContext.Sailpoint on r.UserName equals s.Username
              where s.LcState == "DELETED"
              select r).ToList() 
                       .GroupBy(r => r.MachineName)
                       .Select(g => g.First());

And I also tried this code:

var result = DBContext.SccmTable
                      .Where(x => DBContext.Sailpoint
                      .Where(y => y.LcState == "DELETED")
                      .Select(y => y.Username)
                      .Contains(x.UserName))
                      .Select(x => x)
                      .Distinct().ToList();

These two methods are working, but they are not efficient. I tried other ways as well, they are working but not fast. I don't know how to make it more efficient.

Is there any other way to improve the performance of these queries?

Thank you!

Answer 1

Most efficient is to use Window Function ROW_NUMBER, but with EF Core you have to use FromSql

var result = DBContext.SccmTable.FromSql(@"
    SELECT 
        ss.*
    FROM (
            SELECT 
                r.*, 
                ROW_NUMBER() OVER (PARTITION BY r.MachineName) AS RN
            FROM SccmTable r
            JOIN Sailpoint s ON r.UserName = s.UserName
            WHERE s.LcState = 'DELETED'
        )
    WHERE ss.RN = 1")
    .ToList();

Less efficient, but With EF Core 6 and higher, should work this query:

var result = (from r in DBContext.SccmTable
              join s in DBContext.Sailpoint on r.UserName equals s.Username
              where s.LcState == "DELETED"
              group r by r.MachineName into g
              select g.First())
              .ToList();

---

If you don't want to leave LINQ and produce most effective SQL, you can use extension linq2db.EntityFrameworkCore (note that I'm one of the creators).

Extension allows using Window Functions in LINQ Query:

var query = 
    from r in DBContext.SccmTable.ToLinqToDB()
    join s in DBContext.Sailpoint on r.UserName equals s.Username
    where s.LcState == "DELETED"
    select new 
    {
        r,
        RN = Sql.Ext.RowNumbert().Over()
            .PartitionBy(r.MachineName)
            .ToValue()
    } into ss
    where ss.RN == 1
    select ss.r;

var result = query.ToList();

Note that it is good to specify how to Order items in MachineName group. Many databases do not allow ROW_NUMBER without ORDER BY part.