Select distinct on one column only using Entity Framework Code First

c# entity-framework sql sql-server

Question

I'm developing an Entity Framework Code First library using C# and .NET Framework 4.0.

I have this table on my database:

enter image description here

SentBy and SentTo columns are FK to a User table.

I want to get users that have sent a message to another user, but I only want to get it once. To do it, I use this SQL Statement:

select distinct SentBy, DateUtcSent
  from Messages where isAFriendshipRequest = 0 and SentTo = 15
order by DateUtcSent DESC;

I do order by DateUtcSent DESC; to get the latest message sent first.

The idea is to get a row for each user that has sent a message to user 15.

But I get this:

enter image description here

As you can see, I get user 2 twice.

I don't need the last row.

Here, I'm trying to get each different user that has sent a message to user 15, and get user id, dateUtcSent and the body on the result.

How can I do it to don't get a sentBy value more than once?

UPDATE

And, If I want to to do the this:

select distinct SentTo, DateUtcSent
  from Messages where isAFriendshipRequest = 0 and SentBy = 15
order by DateUtcSent DESC;

And then, merge both results without repeating users and getting the last message.

How can I do it?

1
1
10/29/2013 9:39:39 AM

Accepted Answer

After our coop, the final query is:

var query =
    context.Messages
    .Where(x => x.IsAFriendshipRequest == false && x.SentTo == userId).Include("SentByUser")
    .Select(x => new { User = x.SentBy, x.DateUtcSent, x.Body, Name = x.SentByUser.Name })
    .Union
    (
        context.Messages
        .Where(x => x.IsAFriendshipRequest == false && x.SentBy == userId).Include("SentToUser")
        .Select(x => new { User = x.SentTo, x.DateUtcSent, x.Body, Name = x.SentToUser.Name })
    )
    .GroupBy(x => new {x.User, x.Name})
    .Select(x => new { User = x.Key.User, DateUtcSent = x.Max(z => z.DateUtcSent), Body = x.Where(z => z.DateUtcSent == x.Max(d => d.DateUtcSent)).Select(z=>z.Body ).SingleOrDefault(), Name = x.Key.Name});
1
10/29/2013 12:15:16 PM

Popular Answer

You can use DistinctBy(m=> m.SentBy)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow