CODEWITHSUNDEEP
c#.netentity-frameworklinqentity-framework-6
I Love Stackoverflow
I Love Stackoverflow

Reputation: 6868

Multiple condition with Entity Framework to get desired record

I am trying to get single record from statistics table with following logic:

1st preference :  Flag = 0
2nd preference :  Flag = 1
3rd preference :  Flag = 2 (only if we dont have records with flag=0 and 1)

Table: Statistics

Id   Zoneid  Emergency   Flag   Date
1    100      0           1     2016-6-01 13:10:05.360
2    100      2           2     2016-6-01 14:10:05.360
3    100      0           2     2016-6-01 15:10:05.360
4    100      2           2     2016-6-01 16:10:05.360
5    100      2           0     2016-6-01 14:10:05.360
6    100      1           2     2016-6-01 13:10:05.360

The logic I am trying to implement is like below:

If(Flag == 0) then  
   take records with highest emergency(order by emergency desc) but if multiple records found then take latest record order by date desc(only 1 ).
else if(flag==1) 
     Take records with highest emergency(order by emergency desc) but if multiple records found then take latest record order by date desc(only 1).
else if (no records with flag==0 and flag==1 found)
    Take records with highest emergency(order by emergency desc) but if multiple records found then take latest record order by date desc(only 1).

Data model:

public partial class Zone
{
    public int Id { get; set; }
    public string Area { get; set; }
    public virtual ICollection<Statistics> Statistics { get; set; }
}

public partial class Statistics
{
    public int Id { get; set; }
    public int ZoneId { get; set; }
    public int Emergency { get; set; }
    public int Flag { get; set; }
    public System.DateTime Date { get; set; }
    public virtual Zone Zone { get; set; }
}

My query:

var statistics= (from z in db.Zone
                 select new 
                 {
                     ZoneName = z.Area,
                     //Not getting how t implement multiple conditions in EF
                     StatisticsId = z.Statistics.Where(t => t.ZoneId == 100 &&)
                                             .Select(t => t.Id).FirstOrDefault() 
                 }

So here I am not getting how to implement all those conditions and get desired statistic record.

Upvotes: 1

Views: 201

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205629

The logic you are describing sounds like simple priority order - first by Flag ascending, then (for equal Flag) by Emergency descending, then (for equal Flag and Emergency) by Date descending, and taking the first record in that order:

StatisticsId = (from s in z.Statistics
                where s.ZoneId == 100
                orderby s.Flag, s.Emergency descending, s.Date descending
                select (int?)s.Id).FirstOrDefault() ?? 0

Upvotes: 2

LocEngineer
LocEngineer

Reputation: 2917

Have a shot with this (warning: untested, don't have the time to re-create the class and all):

var statistics= (from z in db.Zone
                 select new 
                 {
                     ZoneName = z.Area,
                     StatisticsId = z.Statistics.OrderBy(t=>t.Flag)
                     .Where(t => t.ZoneId == 100).Select(t => t.Id).FirstOrDefault() 
                 });

Upvotes: 1

Related Questions