CODEWITHSUNDEEP
asp.net-mvclinqasp.net-mvc-4asp.net-mvc-3asp.net-mvc-2
Xtremcool
Xtremcool

Reputation: 185

How to make use of the conditional statement in where clause?

Here is my below code

string CampusId = "1";
string ClassId = "1";
var Class = GetClassesselect();
var model = (from sr in db.StudentRequests
join c in db.Classes
on sr.ClassId equals c.ClassId
where 
(CampusId ==""?"0": CampusId)
&& (ClassId == "" ? "0" : ClassId)
orderby sr.CreatedOn descending
select new Studentvm
{   
PaymentMethod = sr.PaymentMethod,
CampusId = (int)ViewData["CampusId"],
ClassId = (int)ViewData["ClassId"],
Availableclass = Class,   
})
.ToList().ToPagedList(page ?? 1, 10);


public IList<SelectListItem> GetClassesselect()
{
    // This comes from database.       
    var Class = db.Classes
        .Select(x => new SelectListItem { Text = x.ClassName, Value = x.ClassId.ToString() })
        .ToList();
    Class.Insert(0, new SelectListItem { Text = "--Please Select--", Value = "" });
    return Class;
}

Here I am trying to make where statement as conditional that if campusid is null then by default it does not count that where condition else where condition should be taken

Example like In sql we are doing some thing as

Select *
from Registration where     
(ISNULL(@CampusId,'')='' OR (R.CampusId = @CampusId ))
AND  (ISNULL(@ClassId,'')='' OR (TC.ClassId =@ClassId))

here in above sql if @CampusId is null then that statement will not be taken as granted in where condition

So I am trying to do same thing in above Linq code how can I do so?

Upvotes: -1

Views: 285

Answers (2)

Ren&#233; Vogt
Ren&#233; Vogt

Reputation: 43916

This does not work with a ternary operator, because the query provider is not able to translate that to SQL.
But if I understand your logic correct, you want to select records where @CampusId is null OR R.CampusId equals @CampusId. So

where 
(CampusId == null || CampusId == sr.CampusId) &&
(ClassId == null || ClassId == sr.ClassId)

Upvotes: 1

Andrei
Andrei

Reputation: 56716

Very straightforward: use your param only if it is not null, and ignore otherwise.

where
    CampusId == null || sr.CampusId == CampusId
    &&
    ClassId == null || sr.ClassId == ClassId

Assuming those are "sr" properties of course.

Upvotes: 1

Related Questions