CODEWITHSUNDEEP
c#linqasp.net-mvc-4asp.net-web-api
Ayman H
Ayman H

Reputation: 185

Construct LINQ query using variables in asp.net WebAPI

I am trying to build a method in my asp.net WebAPI to grab data based on the arguments passed on the method. The method is used to perform a search on restaurant data. I have a variable called 'type' that determines the type of data search performed. The second variable 'keyword' is the keyword searched by the user. The WHERE condition in my LINQ query depends on the type and needs to be dynamic, so I have used a separate variable outside the LINQ query to define the condition. I have tried assigning this variable to my WHERE statement on the LINQ query but it doesn't seem to work. Can someone help with it please? I have been stuck on this for a few days now

public IQueryable<RestaurantView> GetRestaurantsForSearch(string keyword, int type, string location)
    {
        //
        var condition = "";
        if(type == 1)
        {
           condition = "x.RestaurantName.Contains(keyword)";
        } else if(type == 2){
            condition = "x.Cuisine.Equals(keyword)"; 
        }
        else {
            condition = "x.Rating.Equals(keyword)";
        }

        var query = from x in db.Restaurants
                    join y in db.Cuisine on x.RestaurantCuisine equals y.CuisineID
                    where condition
                    select new RestaurantView
                    {
                        RestaurantID = x.RestaurantID,
                        RestaurantName = x.RestaurantName,
                        RestaurantCuisine = y.CuisineName,
                        RestaurantDecription = x.RestaurantDecription
                    };


        return query;
    }

Upvotes: 0

Views: 6107

Answers (4)

Vinoth Narayan
Vinoth Narayan

Reputation: 275

                  var query = from x in db.Restaurants
                join y in db.Cuisine on x.RestaurantCuisine equals  y.CuisineID
                where condition
                select new RestaurantView
                {
                    RestaurantID = x.RestaurantID,
                    RestaurantName = x.RestaurantName,
                    RestaurantCuisine = y.CuisineName,
                    RestaurantDecription = x.RestaurantDecription
                };


    return query;
}

how to get the return query value in client side to assign for grid view binding

Upvotes: 0

Mister Epic
Mister Epic

Reputation: 16743

If you are ok with dropping your comprehensive LINQ query in favour of the extension method syntax, it's pretty simple (I'm on a netbook without VS, so I apologize that this is untested but should give you the idea):

var query = db.Restaurants
            .Include("Cuisine")

if(type == 1)
{
    query= query.Where(x => x.RestaurantName.Contains(keyword));
} 
else if(type == 2)
{
    query = query.Where(x => x.Cuisine == keyword); 
}
else {
    query = query.Where(x => x.Rating == keyword);
}

This builds out your expression tree differently based on your logic checks, which will result in a different SQL query being generated based on the value of type.

I notice that in your join, Cuisine appears to be an Entity, but in your logic checks, you attempt to filter by comparing Cuisine to a string so I think there is some disconnect.

Upvotes: 1

CSJ
CSJ

Reputation: 3951

Try this:

Predicate<Restaurant> pred;
if (type == 1) pred = x => x.RestaurantName.Contains(keyword);
else if (type == 2) pred = x => x.Cuisine.Equals(keyword);
else pred = x => x.Rating.Equals(keyword);

var query = from x in db.Restaurants
            join y in db.Cuisine on x.RestaurantCuisine equals y.CuisineID
            where pred(x)

            select new RestaurantView
            {
                RestaurantID = x.RestaurantID,
                RestaurantName = x.RestaurantName,
                RestaurantCuisine = y.CuisineName,
                RestaurantDecription = x.RestaurantDecription
            };
return query;

Upvotes: 3

COLD TOLD
COLD TOLD

Reputation: 13599

You need to look a dynamic linq library i think then you can execute string statements inside your linq

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

or you can execute direct query

http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executequery.aspx

Upvotes: 1

Related Questions