How to convert this SQL to LINQ in C#?

Question

I have a query in SQL query as like below:

with pagedetail as
(
    select  
        c.componentrefid, l.name, c.startdate,
        ROW_NUMBER() over(PARTITION By c.componentrefid order by c.startdate desc) as rownumber 
    from 
        FM_componentTransaction as c 
    inner join 
        FM_LK_statusinfo as l on c.Statusinforefid = l.refid
    inner join 
        fm_scriptinfo as s on s.Refid = c.ScriptRefId
    where 
        s.CustomerInfoRefId = '85629125-7072-4EFE-9201-97E088E126C6'
)
select 
    pd.* 
from 
    pagedetail pd 
where 
    pd.rownumber = 1

I can get the output of this. Now my questions is how to implement this query using Entity Framework?

where is your query? – Billal Begueradj Apr 20 '16 at 04:48 — Billal Begueradj, Apr 20 '16 at 04:48
I edited now. Please check it – aaroki Apr 20 '16 at 04:49 — aaroki, Apr 20 '16 at 04:49

score 2 · Answer 1 · edited May 23 '17 at 11:45

2

I know this is not a direct answer to your question, however one approach would be to create a stored procedure in SQL, and then just call the stored procedure in Entity Framework.

Code first: How to call Stored Procedure in Entity Framework 6 (Code-First)?

Database First: https://msdn.microsoft.com/en-us/data/gg699321.aspx

edited May 23 '17 at 11:45

Community

1
1

answered Apr 20 '16 at 05:01

Zapnologica

22,170
44
158
253

yes zapnologica. i m doing this by stored procedure – aaroki Apr 20 '16 at 05:26
Then all you need to do, is create a Database first model of your database. And then when you instantiate a new instance of your `Db Context` you can go `dbContext.StoredprocedureName` – Zapnologica Apr 21 '16 at 09:25

score 1 · Answer 2 · answered Apr 20 '16 at 05:21

Assuming you have the following model:

public class ComponentTransaction
{
    public Guid componentrefid { get; set; }
    public string name { get; set; }
    public DateTime startdate { get; set; }
    public Guid Statusinforefid { get; set; }
    public Guid ScriptRefId { get; set; }
}

public class Statusinfo
{
    public Guid refid { get; set; }
}

public class Scriptinfo
{
    public Guid refid { get; set; }
    public Guid CustomerInfoRefId { get; set; }
}

The code can look like this:

Db db = new Db();
Guid customerInfoRefId = new Guid("85629125-7072-4EFE-9201-97E088E126C6");
var res = db.ComponentTransactions
    .GroupBy(c => c.componentrefid)
    .Select(g => g.OrderByDescending(c => c.startdate).First())
    .Join(db.Statusinfos, c => c.Statusinforefid, l => l.refid, (c, l) => c)
    .Join(db.Scriptinfos.Where(s => s.CustomerInfoRefId == customerInfoRefId), 
        c => c.ScriptRefId, s => s.refid, (c, s) => c);

Thank you ivan. let me try this soltuion – aaroki Apr 20 '16 at 05:27 — aaroki, Apr 20 '16 at 05:27

How to convert this SQL to LINQ in C#?

2 Answers2