Construct LINQ query using variables in asp.net WebAPI

Question

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;
    }

Please read on Expression (i.e. start from links in Queryable.Where and update you question to something like "how to build Expression to compare single property for LINQ to SQL". — Alexei Levenkov
– Alexei Levenkov, Commented Aug 24, 2013 at 18:43

CSJ · Accepted Answer · 2013-08-24 18:48:09Z

3

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;

answered Aug 24, 2013 at 18:48

CSJ

4,0212 gold badges23 silver badges32 bronze badges

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

2 Comments

Mister Epic Over a year ago

This is another way of altering how your expression tree gets built out. I prefer doing it this way or with my answer over dynamic LINQ because I appreciate keeping things strongly-typed.

Ayman H Over a year ago

Thanks @CSJ. I tried using your method but I get the following exception

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.","ExceptionType":"System.NotSupportedException","StackTrace":"   at System.Data.Objects.ELinq.ExpressionConverter.NotSupportedTranslator.Translate(ExpressionConverter parent, Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)

COLD TOLD · Accepted Answer · 2013-08-24 18:46:22Z

1

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

answered Aug 24, 2013 at 18:46

COLD TOLD

13.6k3 gold badges37 silver badges53 bronze badges

3 Comments

asymptoticFault Over a year ago

Dynamic Linq is the way to go. I use in my app to construct completely dynamic queries and it really cut down on the coding. You can create a where condition from the string like .Where(String.Format("RestaurantName.Contains({0})", keyword)).

Ayman H Over a year ago

Thanks @COLD TOLD, Dynamic Linq is surely the way to go! Worked for me perfectly!

Jack Over a year ago

This reminds me too much of manually building queries with strings which defeats the purpose of LINQ IMO. The predicate builder or Chris Hardies answer feels cleaner.

Mister Epic · Accepted Answer · 2013-08-24 19:38:24Z

1

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.

answered Aug 24, 2013 at 19:38

Mister Epic

16.8k13 gold badges88 silver badges156 bronze badges

1 Comment

Ayman H Over a year ago

Thanks @Chris Hardie, I ended up using Dynamic Linq but your answer is much appreciated

Vinoth Narayan · Accepted Answer · 2015-03-09 04:28:40Z

0

                  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

answered Mar 9, 2015 at 4:28

Vinoth Narayan

2752 silver badges16 bronze badges

Collectives™ on Stack Overflow

Construct LINQ query using variables in asp.net WebAPI

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related