Select distinct on one column only using Entity Framework Code First

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?

Bugs · Accepted Answer · 2017-12-12 12:53:36Z

3

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

edited Dec 12, 2017 at 12:53

Bugs

4,4799 gold badges34 silver badges41 bronze badges

answered Dec 12, 2017 at 12:36

mark

393 bronze badges

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

Comments

Giannis Paraskevopoulos · Accepted Answer · 2013-10-29 12:15:16Z

1

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});

edited Oct 29, 2013 at 12:15

answered Oct 29, 2013 at 9:30

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

7 Comments

VansFannel Over a year ago

I have updated my question with more information about the last comment I sent you.

Giannis Paraskevopoulos Over a year ago

Check my new edit. After the union just group by again and get the max date.

VansFannel Over a year ago

I'm not sure if I did it correctly, but I need to get Messages.Body and Users.Name. This is how I modified your code: pastebin.com/azEHK5iT. It is a bit slow.

Giannis Paraskevopoulos Over a year ago

I cannot comment on the speed of it, but i made some modifications pastebin.com/4cKpiWiQ . I added the Name on the group by and then you need a Where to get the correct Body or else you would take just one Body in random.

VansFannel Over a year ago

Thanks but it doesn't work: T The specified method 'Boolean Max[<>f__AnonymousType14,Boolean](System.Collections.Generic.IEnumerable1[<>f__AnonymousType14[System.Nullable1[System.Int32],System.DateTime,System.String,System.String]], System.Func2[<>f__AnonymousType14[System.Nullable`1[System.Int32],System.DateTime,System.String,System.String],System.Boolean])' in type 'System.Linq.Enumerable' can not be converted into a store expression LINQ to Entities because no overload matches the arguments passed.

|

Cyril Gandon · Accepted Answer · 2013-10-29 09:40:52Z

0

Your query is SQL, but I understand that you need a Linq To Sql query, so here it is:

var q = from message in Messages 
        where message.isAFriendshipRequest = 0 
        && message.SentTo = 15
        group message by message.SentBy into grp
        select grp.OrderByDescending(m => m.DateUtcSent).First();

You group by sender, order by date, and take only the first.

edited Oct 29, 2013 at 9:40

answered Oct 29, 2013 at 9:32

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

Collectives™ on Stack Overflow

Select distinct on one column only using Entity Framework Code First

UPDATE

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related