How to transfer SQL to LINQ or other methods-CodePudding

so I have been trying to execute a query for a while. This is the query:


WITH ranked_loggings AS( 
    SELECT m.*, ROW_NUMBER() OVER(PARTITION BY proportioning_dbid ORDER BY logging_dbid DESC) AS rn
    FROM logging AS m ) 
SELECT* FROM ranked_loggings WHERE rn = 1;

I've tried many solution like http://www.sqltolinq.com/ and http://www.linqpad.net/. These unfortunately don't support PostgreSQL to my knowledge. Now I have tried to execute the query raw. Now I am not able to get the result and my application keeps crashing. Can someone point me in the right direction or am I completely missing something here?

CodePudding user response：

Figured it out. Apparently it wasn't possible to use raw SQL without parameters. Solved it like this:

using (var context = new AmadeusDBContext())
            {
                ICollection<Logging> loggings = context.Loggings
                                    .FromSqlRaw("WITH ranked_loggings AS( "  
                                    "SELECT m.*, ROW_NUMBER() OVER(PARTITION BY proportioning_dbid ORDER BY logging_dbid DESC) AS rn "  
                                    "FROM logging AS m ) "  
                                    "SELECT* FROM ranked_loggings WHERE rn = 1;", new NpgsqlParameter("@parameterrequired", 01))
                                    .ToList<Logging>();

                return loggings;
            }

CodePudding user response：

EF Core should generate needed result from this LINQ Query:

var loggings = context.Loggings;

var query =
    from d in loggings.Select(m => new { m.proportioning_dbid }).Distinct()
    from m in loggings
        .Where(m => m.proportioning_dbid == d.proportioning_dbid)
        .OrderByDescending(m => m.logging_dbid)
        .Take(1)
    select m;