Enum Condition in Linq Query in C#

Question

I want to add Enum Condition in Linq Query even if Enum datamember is null or empty

I had added ALL in model for Filter purpose so that if user select ALL then all data should be displayed

Data Model :

    public partial class AuditTable
    {
        public int ID { get; set; }
        public int CompanyId { get; set; }
        public int KeyFieldID { get; set; }
        public System.DateTime DateTimeStamp { get; set; }
        public EntityType DataModel { get; set; }
        public string ValueBefore { get; set; }
        public string ValueAfter { get; set; }
        public string Changes { get; set; }
        public AuditActionType AuditActionTypeENUM { get; set; }

        public int EmployeeId { get; set; }
        public string EmployeeCode { get; set; }
        public string Remarks { get; set; }
        public string IPAddress { get; set; }
        public string UserName { get; set; }
    }
 public enum AuditActionType
    {
        All = 1,
        Create,
        Update,
        Delete
    }

    public enum EntityType
    {
        All = 1,
        BasicDetails,
        EmployeeDetails,
        PersonalDetails                
    }

Below code is working fine but had to repeat same query 4 times. I want to combine below query into one

if (eType == EntityType.All)
 if (aType == AuditActionType.All)
  AuditTrail = ent.tblAuditTable.Where(s => s.KeyFieldID == ID && s.EmployeeCode.Contains(code) && s.UserName.Contains(username)).OrderByDescending(s => s.DateTimeStamp).ToList(); 
 else
  AuditTrail = ent.tblAuditTable.Where(s => s.KeyFieldID == ID && s.AuditActionTypeENUM == aType && s.EmployeeCode.Contains(code) && s.UserName.Contains(username)).OrderByDescending(s => s.DateTimeStamp).ToList(); 
else
if (aType == AuditActionType.All)
 AuditTrail = ent.tblAuditTable.Where(s => s.KeyFieldID == ID && s.DataModel == eType && s.EmployeeCode.Contains(code) && s.UserName.Contains(username)).OrderByDescending(s => s.DateTimeStamp).ToList();
else
 AuditTrail = ent.tblAuditTable.Where(s => s.KeyFieldID == ID && s.AuditActionTypeENUM == aType && s.DataModel == eType && s.EmployeeCode.Contains(code) && s.UserName.Contains(username)).OrderByDescending(s => s.DateTimeStamp).ToList();

Jehof · Accepted Answer · 2016-04-07 09:41:09Z

4

You can chain Where() statements.

var baseQuery = ent.tblAuditTable.Where(s => s.KeyFieldID == ID && s.EmployeeCode.Contains(code) && s.UserName.Contains(username));

var realQuery = baseQuery;

if (eType != EntityType.All){
  realQuery = realQuery.Where(s=>s.DataModel == eType);
}

if (aType != EntityType.All){
  realQuery = realQuery.Where(x=>s.AuditActionTypeENUM == aType);
}

var result = realQuery.OrderByDescending(s => s.DateTimeStamp).ToList()

AuditTrail = result;

edited Apr 7, 2016 at 9:41

answered Apr 7, 2016 at 9:35

Jehof

35.7k11 gold badges129 silver badges156 bronze badges

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

Comments

Leandro Soares · Accepted Answer · 2016-04-07 11:20:27Z

1

All together in one query?

Like this:

bool eTypeIsAll = eType == EntityType.All;
bool aTypeIsAll = aType == AuditActionType.All;
AuditTrail =
    ent.tblAuditTable
       .Where(s => s.KeyFieldID == ID
                   && (
                       eTypeIsAll ? (
                            (
                                !aTypeIsAll ?
                                    s.AuditActionTypeENUM == aType
                                    : true
                            )

                       )
                       : (
                            (
                                !aTypeIsAll ?
                                    s.AuditActionTypeENUM == aType
                                    : true
                            )
                            && s.DataModel == eType

                       )

                   )
                   && s.EmployeeCode.Contains(code)
                   && s.UserName.Contains(username))
        .OrderByDescending(s => s.DateTimeStamp)
        .ToList();

Personally, i prefer this way. But many people say that @Jehof method is more readable.

edited Apr 7, 2016 at 11:20

answered Apr 7, 2016 at 9:36

Leandro Soares

2,9823 gold badges29 silver badges40 bronze badges

3 Comments

Ivan Stoev Over a year ago

Not only the other way is more readable, but also generates better SQL queries (with the current query providers).

Leandro Soares Over a year ago

I tought the same, but I've testes several different approachs in my playground and didn't find a big difference, just 5-10ms from query to query... And since you will not read the SQL query by yourself it doesn't really matter.

Leandro Soares Over a year ago

I think it's more about personal preference

Anestis Kivranoglou · Accepted Answer · 2016-04-07 09:39:32Z

0

You should work with IQueryables. There is no need to write your query in one single line. You can Spread it in multiple sub queries.

var queryAble= ent.tblAuditTbale.where(s=>s.KeyFieldId ==Id);

if(eType !=EntityType.All)  
 queryAble = queryAble.where(s=> s.DataModel==eTYpe);

If(aType!= AuditActionType.All)    
 queryAble = queryAble.where(s=> s.AuditActionTypeENUM == aType);

Finnally (Query will execute now)

AuditTrail = queryAble.ToList();

answered Apr 7, 2016 at 9:39

Anestis Kivranoglou

8,2545 gold badges47 silver badges48 bronze badges

Collectives™ on Stack Overflow

Enum Condition in Linq Query in C#

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related