Convert SQLServer sql to Linq

Question

I have a SQL to view table master and detail. How convert to linq. Please help me :

Select B.idchatuser,B.Nama, B.Hp,
(select TOP 1 CreatedTime from ChatUserConversation 
where IdChatUser = B.IdChatUser order by CreatedTime) CreatedTime
from ChatUser B 
order by CreatedTime desc

Please make an attempt, Arly. We are not a code-writing service. The query is relatively simple, you should be able to make a start. — Rob
– Rob ♦, Commented Jun 20, 2016 at 2:14
@Kason : Thanks for the solution, i have downloaded and tried Linqer. But I do not understand how to use — Arly
– Arly, Commented Jun 20, 2016 at 2:40

mshsayem · Accepted Answer · 2016-06-20 04:37:22Z

2

How about this?

from B in ChatUser
join C in ChatUserConversation on B.IdChatUser equals C.IdChatUser
group new {B,C} by new {B.IdChatUser, B.Nama, B.Hp} into g
let CreatedTime = g.OrderBy(x=>x.C.CreatedTime).Select(x=>x.C.CreatedTime).FirstOrDefault()
orderby CreatedTime descending
select new 
{
    IdChatUser = g.Key.IdChatUser, 
    Nama = g.Key.Nama, 
    Hp = g.Key.Hp, 
    CreatedTime = CreatedTime
}

edited Jun 20, 2016 at 4:37

answered Jun 20, 2016 at 2:42

mshsayem

18.1k11 gold badges65 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arly Over a year ago

Thank you,,, this work but the results are not ordered by CreatedTime descending

Hari Prasad · Accepted Answer · 2016-06-20 02:45:24Z

1

Assuming you have ChatUser and ChatUserConversation C# entities, you could do this.

var results = ChatUser.Select(x=> new 
         {
             x.idchatuser,
             x.Name,
             x.Hp,
             CreatedTime =  ChatUserConversation .Where(c=> c.IdChatUser = x.IdChatUser)
                                                 .Max(c=>c.CreatedTime)  
         })
        .ToList();

answered Jun 20, 2016 at 2:45

Hari Prasad

17k4 gold badges23 silver badges35 bronze badges

3 Comments

Arly Over a year ago

i have tried this solution, but don't know how parsing the results to view. i use asp.net mvc C#

Hari Prasad Over a year ago

You mean query is working, but don't know how to use it in view?

Arly Over a year ago

i don't know working or not because when i debug i get error in view

Collectives™ on Stack Overflow

Convert SQLServer sql to Linq

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related