CODEWITHSUNDEEP
c#linq-to-entities
xhedgepigx
xhedgepigx

Reputation: 350

C# Linq to Entites, take take 1st Entity from set of Entities with same Date

I have a table that looks like:
Key Date ... Name ... Status ...
(ID) May ...
(ID) May ...
(ID) May ...
(ID) June ...
(ID) June ...
(ID) June ...
(ID) Jul ...
(ID) Jul ...

What I need is to take the entities in bold as that is the overall property for the set. So it looks like:

(ID) May ...
(ID) June ...
(ID) Jul ...

The Entity Set is retrieved with:

var status = from a in ObjectContext.InspectionReadings
                     where a.InspectionID == new Guid(inspID)
                     && a.DateTaken > before
                     && a.DateTaken <= DateTime.Now
                     orderby a.DateTaken descending, a.Status descending
                     select a;

Now i just need to filter out the ones I don't want.

How can I do this?

Thank you

Upvotes: 1

Views: 89

Answers (3)

Brad Westness
Brad Westness

Reputation: 1572

You could write an IEqualityComparer that only looks at the DateTaken field, and then use Distinct() like this:

public class DateTakenComparer : IEqualityComparer<InspectionReading>
{
    public bool Equals(InspectionReading x, InspectionReading y)
    {
        return x.DateTaken == y.DateTaken;
    }

    public int GetHashCode(InspectionReading obj)
    {
        return obj.GetHashCode();
    }
}

And then:

var statuses = ObjectContext.InspectionReadings
    .Distinct(new DateTakenComparer())
    .OrderByDescending(x => x.DateTaken)
    .ToList();

Upvotes: 2

casperOne
casperOne

Reputation: 74530

You can accomplish this with a GroupBy as well as a call to First.

For example, given your original query, status, you can do:

var firstStatuses = 
    status.GroupBy(i => i.DateTaken).Select(g => g.First());

Note that LINQ-to-Entities might not be able to properly group the data, based on requirements for the select clause in your underlying database.

With that, you can always call AsEnumerable to force the query to occur on objects in memory:

var firstStatuses = 
    status.AsEnumerable().GroupBy(i => i.DateTaken).Select(g => g.First());

One caveat, you should probably order each group depending on which item you want to be returned (if they aren't already ordered correctly).

Upvotes: 2

bluevector
bluevector

Reputation: 3493

You need to use the group by clause...

var status = from a in ObjectContext.InspectionReadings
             where a.InspectionID == new Guid(inspID)
             && a.DateTaken > before
             && a.DateTaken <= DateTime.Now
             group a by a.DateTaken.Month into g
             from m in g
             select new 
             {
               g.Key, g.OrderBy(x => x.DateTaken).First()
             };

Upvotes: 2

Related Questions