Complex extension method - getting an error

Question

Hi everybody,

This code, I believe, is taken from

http://www.codeproject.com/Articles/30588/ASP-NET-MVC-Flexigrid-sample (look close to the bottom at ExtensionMethods)

public static IQueryable<T> Like<T>(this IQueryable<T> source, string propertyName, string keyword)
        {
            var type = typeof(T);
            var property = type.GetProperty(propertyName);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var constant = Expression.Constant("%" + keyword + "%");
            MethodCallExpression methodExp = Expression.Call(null, typeof(SqlMethods).GetMethod("Like", new Type[] { typeof(string), typeof(string) }), propertyAccess, constant);
            Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(methodExp, parameter);
            return source.Where(lambda);
        }

I believe it used to work, but now I am getting this error

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       ....

I deleted part of the stack trace. I am not sure how to fix it and why it was working before and stopped now (although it was a while when I've tested it).

I think I can probably switch to Contains, but I am not certain how to adjust the code above.

Thanks a lot in advance.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 1

This is how it gets called and the last line produces that error

 if (!string.IsNullOrEmpty(qtype) && !string.IsNullOrEmpty(query))
            {
                clients = clients.Like(qtype, query);
            }

            int Total = clients.Count();

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi,

It seems the article uses Linq To SQL while you are using Linq To Entities ? What is your intent ?

It seems the intent is to create a SQL LIKE query. I would just try string.Contains(string) which should already be translated to a LIKE query...

---

Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

Answer 3

... does not recognize the method 'Boolean Like(System.String, System.String)' method ...

IQueryable<T> and Boolean are not the same type.

•

Answer 4

My problem is that I pass the column's name and a keyword. So, my method should somehow accept the column's name. How can I write contains?

In other words, here is what I have:

qtype = columnName

query = 'Typed keyword'

I do not want to use switch method to do something like

switch (qtype)

   case "Name":

        clients = clients.Where(c=>c.Name.Contains(query));

        break;

    case "Contact1":

      ...

In other words, is there some way to make it generic, so the Name in the above will be a variable qtype?

         

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

In other words, is there some way to make it generic, so the Name in the above will be a variable qtype?

Sure, you can use variables as case labels ... in PowerShell!

In C#, case labels must be constants, and a variable ... is not constant.

It seems that you're asking a new question in the same thread.

In this case you should start a new thread to follow forum etiquete.

•

 

Answer 6

It is not a new question, it's exactly my problem as described in this thread.

My question is - how can I adjust the above extension method so it will work correctly for Entity Framework. Or alternatively is where a way to fix how I call that method from my code?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Aproador,

If you have nothing helpful to add to this thread, do not reply.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

I'll take a stab in the dark, untested, untried, no real confidence (but, hey, at least no animals were harmed during the testing!)

public static IQueryable<T> Like<T>(this IQueryable<T> source, string propertyName, string keyword)
{
  var type = typeof(T);
  var property = type.GetProperty(propertyName);
  var parameter = Expression.Parameter(type, "p");
  var propertyAccess = Expression.MakeMemberAccess(parameter, property);
   var constant = Expression.Constant(keyword);
   MethodCallExpression methodExp = Expression.Call(null, typeof(SqlMethods).GetMethod("Contains", new Type[] { typeof(string), typeof(string) }), propertyAccess, constant);
    Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(methodExp, parameter);
    return source.Where(lambda);
        }

I'd actually prefer to change the method name to Contains (in fact it always should have been Contains because that is what it has always done)

---

Paul Linton

Answer 9

Paul,

Thanks a lot, I am going to try it. I've been buried last hour trying to understand

this link and several other links and stackoverflow questions on this topic.

I am also going to leave that original Like alone and try yours Contains.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 10

It produces an error on the MethodCallExpression line and I think the reason is that we're using typeof(SqlMethods) here.

The error is

System.ArgumentNullException was unhandled by user code
  HResult=-2147467261
  Message=Value cannot be null.
Parameter name: method
  Source=System.Core
  ParamName=method
  StackTrace:
       at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, Expression arg0, Expression arg1)
       at CardNumbers.Models.ExtensionMethods.Contains[T](IQueryable`1 source, String propertyName, String keyword) in c:\Dev\V40\CardNumbers\CardNumbers.Web\Models\ExtensionMethods.cs:line 48
     -----------------------

I am going to break till Saturday night now, hopefully you can figure this out.

Thanks again.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

It is not a new question, it's exactly my problem as described in this thread.

My question is - how can I adjust the above extension method so it will work correctly for Entity Framework. Or alternatively is where a way to fix how I call that method from my code?

---

NOmiao,

Your original post is about the error you're getting, and has nothing to do with your 2nd question:  switch (qtype) ... case "Name": ... etc...

In fact, your original post has no explicit question, has no complete specification, does not specify client variable, and seems to come from a newbie that doesn't understand what he's doing.

In brief, it's bad formulated; if you don't know to formulate a question, you'd better ask someone to do it for you.

Moreover, I really have nothing to say when the question is bad formulated.

By the way, there's a forum etiquete to follow: if you have a 2nd question, you should start a new thread, just to follow forum etiquete

•

Answer 12

@Arpoador, the forum etiquette says that you should not make fun of people or their names just because you can.

You don't need to write or do anything because she didn't call your name and ask your help.

I think that her post is just fine, instead of pointing out ten times in your post that her post isn't formulated you can ask her what details you need to help her out.

@Naomi, I don't think you can use Contains because SqlMethods does not have such a method, this is the reason you're getting ArgumentNullException.

You can try this.

public static IQueryable<TSource> Like<TSource>(this IQueryable<TSource> source, string propertyName, string keyword)
		{
			ParameterExpression parameter = Expression.Parameter(source.ElementType);

			MemberExpression left = Expression.Property(parameter, propertyName);
			
			Expression right = Expression.Constant(keyword);

			MethodCallExpression contains = Expression.Call(left, "Contains", null, right);

			var exp = Expression.Lambda<Func<TSource, bool>>(contains, new[] { parameter });

			return source.Where(exp);
		}

---

Regards,

Eyal Shilony

Answer 13

@el sh*tlony,

Nobody needs to know your conceptions about something has nothing to do with you.

Good education manner says you should be quiet unless you're asked something; but you're not an example of educated person. You're just a coward protected by mvps.

Just to let you know, when I write etiquette in my computer, it automatically corrects it to etiquete (also with many other words) and its a big pain to correct all words to correct english spelling. But this is something your IQ doesn't let you understand. But your not a good example of english gramar connoisseur. You must remember the word comprede.

You were not called to explain your point of view; nobody asked you to do so.

Just shut your mouth up and cross your hands, and go read C# language specification, to not write stupid code. Start by is and as operators.

•

Answer 14

Wow! it's actually you again.

I feel sorry for you, really, changing names so no one will recognize you must be really sad, right? crawl back to your Brazilian cave.

Just because the computer supposedly correct your spelling doesn't mean you can't change it but obviously you didn't! because you thought it's the correct word, so go lie to someone else.

BTW, it should be English not english get some education and learn the differences, the computer fixed that too, right?

I notice a pattern here, every time you write a post you bother to bring up the old subjects you were arguing about because your old posts are either locked or the accounts got banned and the reason is demonstrated in this thread, you're speaking about education and in the same post you're acting like a vulgar person who grew up in the wild forests of Brazil.

---

Regards,

Eyal Shilony

Answer 15

I got a solution from another forum by Rob Jasinski which I'll quote verbatim:

Anything in SqlMethods is for Linq to Sql queries only (see http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.aspx). You don't really need it anyways since you can use the Contains method of the string class:

public static IQueryable< T> Like< T>(this IQueryable< T> source, string propertyName, string keyword)
{
	var type = typeof(T);
	var property = type.GetProperty(propertyName);
	var parameter = Expression.Parameter(type, "p");
	var propertyAccess = Expression.MakeMemberAccess(parameter, property);
	var constant = Expression.Constant(keyword);

	var like = typeof(string).GetMethod("Contains",
			   new Type[] { typeof(string)});
	MethodCallExpression methodExp = Expression.Call(propertyAccess, like, constant);
	Expression< Func< T, bool>> lambda =
		  Expression.Lambda< Func< T, bool>>(methodExp, parameter);
	return source.Where(lambda);
}

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog