Linq extension method error

[Andrus]

I tried to create my extension method:

Northwind db = CreateDB();
var q = db.Customers.StartsWith("CompanyName", "a");

but got exception in LambdaExpression.Call() :

No method 'StartsWith' on type 'System.String' is compatible with the
supplied arguments.

Any idea how to fix this ?

Andrus.


static class StartWithExtension {
public static IQueryable<TEntity> StartsWith<TEntity>(this
IQueryable<TEntity> query, string propertyName, string value) {

ParameterExpression param = Expression.Parameter(typeof(TEntity), "p");

MethodCallExpression testExp = LambdaExpression.Call(
Expression.Property(param, propertyName),
"StartsWith",
new Type[] { typeof(string), typeof(System.StringComparison) },
new Expression[] { Expression.Constant(value),
Expression.Constant(System.StringComparison.CurrentCultureIgnoreCase) });
return query.Where(Expression.Lambda<Func<TEntity, bool>>(testExp, param));
}
}

 

[Nicholas Paldino [.NET/C# MVP]]

Andrus,

Why are you doing this? Why not just do:

var q = db.Customers.Where(c => c.CompanyName.StartsWith("a"),
System.StringComparison.CurrentCultureIgnoreCase);

LINQ to SQL should take care of the rest.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I'm not sure what you want to do, I assume you are just playing around with
it.
I modified a little bit your code (I had no LINQ-to-SQL) so I used ling to
objects:


static class StartWithExtension
{
public static IEnumerable<TEntity> StartsWith<TEntity>(this
IEnumerable<TEntity> query, string propertyName, string value)
{

foreach (TEntity item in query)
if (item.GetType().GetProperty(propertyName).GetValue(item,
null).ToString().StartsWith(value))
yield return item;

}
}

List<C> GetC()
{
return new List<C>() { new C() { Name = "1" }, new C() { Name =
"2" }, new C() { Name = "23" } };
}
void Q()
{
List<C> list = GetC();
var f = list.StartsWith("Name", "2");
foreach (C c in f)
Console.WriteLine(c.Name);
}


class C
{
string s;


public string Name
{
get
{
return s;
}
set
{
s = value;
}
}

}

 

[Marc Gravell]

How about as below? I don't know what DbLinq will make of it though...
but in theory it should work ;-p

Marc

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

class MyItem
{
public string CompanyName { get; set; }
}
static class Program
{
static void Main()
{
var items = new List<MyItem> {
new MyItem {CompanyName = "foo"},
new MyItem {CompanyName = "bar"},
new MyItem {CompanyName = "freds"}
};
IQueryable<MyItem> source = items.AsQueryable();

foreach (var item in source.StartsWith("CompanyName", "f"))
{
Console.WriteLine(item.CompanyName);
}
}
public static IQueryable<T> StartsWith<T>(this IQueryable<T>
source, string property, string value) {
ParameterExpression obj = Expression.Parameter(typeof(T),
"x");
ConstantExpression val = Expression.Constant(value,
typeof(string));
Expression prop = Expression.Property(obj, property);
Expression call = Expression.Call(prop,
typeof(string).GetMethod("StartsWith", new[] {typeof(string)}), val);

return source.Where(Expression.Lambda<Func<T, bool>>(call,
obj));
}
}

 

[Marc Gravell]

(reposted in case NNTP ate it)

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

class MyItem
{
public string CompanyName { get; set; }
}
static class Program
{
static void Main()
{
var items = new List<MyItem> {
new MyItem {CompanyName = "foo"},
new MyItem {CompanyName = "bar"},
new MyItem {CompanyName = "freds"}
};
IQueryable<MyItem> source = items.AsQueryable();

foreach (var item in source.StartsWith("CompanyName", "f"))
{
Console.WriteLine(item.CompanyName);
}
}
public static IQueryable<T> StartsWith<T>(this IQueryable<T>
source, string property, string value) {
ParameterExpression obj = Expression.Parameter(typeof(T),
"x");
ConstantExpression val = Expression.Constant(value,
typeof(string));
Expression prop = Expression.Property(obj, property);
Expression call = Expression.Call(prop,
typeof(string).GetMethod("StartsWith", new[] {typeof(string)}), val);

return source.Where(Expression.Lambda<Func<T, bool>>(call,
obj));
}
}

 

[Marc Gravell]

I don't know what DbLinq will make of it though...

but in theory it should work ;-p

For completeness, I've verified this snippet against LINQ-to-SQL and
SQL-Server 2005 Express Edition, using "pubs" as an example (below);
if DbLinq still refuses to work with the properties (like it did with
Where), then I suggest you feed this to the DbLinq developer, as this
is a valid test-case: it looks OK and works for LINQ-to-SQL and
LINQ-to-objects: it /should/ work for DbLinq.

Marc

C#:
using (pubsDataContext dc = new pubsDataContext())
{
var query = dc.authors.StartsWith("au_lname", "b")
.OrderBy(x => x.au_lname).ThenBy(x => x.au_fname)
.Select(x => new { Forename = x.au_fname, Surname
= x.au_lname });
Console.WriteLine(dc.GetCommand(query).CommandText);
// SQL
foreach (var auth in query) // Results
{
Console.WriteLine("{1}, {0}", auth.Forename,
auth.Surname);
}
}

SQL:

SELECT [t0].[au_fname] AS [Forename], [t0].[au_lname] AS [Surname]
FROM [dbo].[authors] AS [t0]
WHERE [t0].[au_lname] LIKE @p0
ORDER BY [t0].[au_lname], [t0].[au_fname]

Results:

Bennet, Abraham
Blotchet-Halls, Reginald

 

[Andrus]

Marc,

My issue is about Linq expression tree generation dynamically. This is not
provider specific.

For completeness, I've verified this snippet against LINQ-to-SQL and
SQL-Server 2005 Express Edition, using "pubs" as an example (below);

Thank you. Unfortunately I have only northwind database ready so my test was
against northwind database.

if DbLinq still refuses to work with the properties (like it did with
Where), then I suggest you feed this to the DbLinq developer, as this is a
valid test-case: it looks OK and works for LINQ-to-SQL and
LINQ-to-objects: it /should/ work for DbLinq.

This was GetProperties() return order issue described in this newsgroup in
other thread. This was fixed in SVN yesterday.
There is no difference between compiler generated and dynamically generated
expression trees. This works.

My issue is:

I need to perform case insensitive search but your code performs case
sensitive search.
I think best way for this is to pass
System.StringComparison.CurrentCultureIgnoreCase as second parameter to
StartsWith method.

I tried this in my code by using

MethodCallExpression testExp = LambdaExpression.Call(
Expression.Property(param, propertyName),
"StartsWith",
new Type[] { typeof(string), typeof(System.StringComparison) },
new Expression[] { Expression.Constant(value),
Expression.Constant(System.StringComparison.CurrentCultureIgnoreCase) });
return query.Where(Expression.Lambda<Func<TEntity, bool>>(testExp, param));
}

But this causes exception about missing method signature.
I have no idea why this exception occurs since
StartsWith( string, System.StringComparison ) method exist.

Why this exception occurs ?

Andrus.

 

[Marc Gravell]

This is not provider specific.
Expression trees, no - but we know from several months on this that
some of the problems you are seeing *are* provider specific.

There is no difference between compiler generated and dynamically
generated expression trees.

Actually, they are subtly different at the IL level -
compiler-generated bings to the "get_{Foo}" MethodInfo, where-as
dynamically binds to the "{Foo}" PropertyInfo (if you see what I
mean) - but they should behave the same to a compliant provider.

I need to perform case insensitive search but your code performs
case sensitive search.

No - your *provider* perfoms a case-sensitive search. LINQ-to-objects
is probably case sensitive, but you can clearly see (from my
LINQ-to-SQL "b" test-case which returns Bennet and Blotchet-Halls)
that this is provider specific - which makes sense when case
sensitivity is a feature (or not) of the database.

Adding the StringComparison option breaks LINQ-to-SQL
(NotSupportedException):
"The method 'StartsWith' has a translation to SQL, but the overload
'Boolean StartsWith(System.String, System.StringComparison)' does
not."

So I'm not in a hurry to try and get a generic Expression to do this,
since it almost certainly won't work anyway... and *at best* it would
be invariant, not current.

The following works for LINQ-to-objects, but not LINQ-to-SQL; I
wouldn't expect it to work on DbLinq; so I say again (re "this is not
provider specific"): the provider is *very* important. LINQ provides a
framework for a common query grammer, but it does not (nor can it)
guarantee that all are equal.

public static IQueryable<T> StartsWith<T>(this IQueryable<T>
source, string property, string value)
{
ParameterExpression obj = Expression.Parameter(typeof(T),
"x");
ConstantExpression val = Expression.Constant(value,
typeof(string));
ConstantExpression comp =
Expression.Constant(StringComparison.InvariantCultureIgnoreCase,
typeof(StringComparison));
Expression prop = Expression.Property(obj, property);
Expression call = Expression.Call(prop,
typeof(string).GetMethod("StartsWith", new[] { typeof(string) ,
typeof(StringComparison)}), val, comp);

return source.Where(Expression.Lambda<Func<T, bool>>(call,
obj));
}

Marc

 

[Marc Gravell]

Following works for forced insensitive in both LINQ-to-objects and
LINQ-to-SQL; I don't like it (it isn't culture-safe for objects), but
might be the closest...

public static IQueryable<T> StartsWith<T>(this IQueryable<T>
source, string property, string value)
{
ParameterExpression obj = Expression.Parameter(typeof(T),
"x");
Expression val = Expression.Constant(value,
typeof(string));
val = Expression.Call(val,
typeof(string).GetMethod("ToUpper", Type.EmptyTypes));
Expression prop = Expression.Property(obj, property);
prop = Expression.Call(prop,
typeof(string).GetMethod("ToUpper", Type.EmptyTypes));

Expression call = Expression.Call(prop,
typeof(string).GetMethod("StartsWith", new[] { typeof(string)}), val);
return source.Where(Expression.Lambda<Func<T, bool>>(call,
obj));
}

 

[Andrus]

Why are you doing this? Why not just do:

var q = db.Customers.Where(c => c.CompanyName.StartsWith("a"),
System.StringComparison.CurrentCultureIgnoreCase);

LINQ to SQL should take care of the rest.

Nicholas,

1. User can select any column in DataGridView and search from this column.
Property name is not known at design time.
Expression you provided does not allow to replace CompanyName with variable
name like in dynamic languages.

2. Marc wrote that this query causes exception in Linq-SQL.

Andrus.

 

[Andrus]

Ignacio,

I'm not sure what you want to do, I assume you are just playing around
with it.

I need to force linq to create case insensitive substring SELECT command
which allows server to use indexes for search like

SELECT * FROM Customers WHERE CompanyName ILIKE 'a%'

I used case insensitive ILIKE operator.

I modified a little bit your code (I had no LINQ-to-SQL) so I used ling to

objects

You are falling back to procedural programming by using foreach.
Is'nt the solution provided by Marc simpler?

public static IQueryable<T> StartsWith<T>(this IQueryable<T>
source, string property, string value)
{
ParameterExpression obj = Expression.Parameter(typeof(T),
"x");
ConstantExpression val = Expression.Constant(value,
typeof(string));
ConstantExpression comp =
Expression.Constant(StringComparison.InvariantCultureIgnoreCase,
typeof(StringComparison));
Expression prop = Expression.Property(obj, property);
Expression call = Expression.Call(prop,
typeof(string).GetMethod("StartsWith", new[] { typeof(string) ,
typeof(StringComparison)}), val, comp);

return source.Where(Expression.Lambda<Func<T, bool>>(call,
obj));
}

Andrus.

 

[Andrus]

Mark,

Actually, they are subtly different at the IL level - compiler-generated
bings to the "get_{Foo}" MethodInfo, where-as dynamically binds to the
"{Foo}" PropertyInfo (if you see what I mean) - but they should behave the
same to a compliant provider.

So it is possible to use dynamic properties added using TypeDescriptor in
queries created at runtime ?

If I create queries using MS Dynamic Linq Library string parser, those
queries will work with dynamic properties ?

Andrus.

 

[Andrus]

using (pubsDataContext dc = new pubsDataContext())

Using statement forces deterministics dispose of dc object. Dispose consumes
additional CPU cycles which are not required.
So this may cause perfomance loss since

So I'm wondering why you use using statement with datacontext. Maybe bad
habit from using connection object ?
It seems to be bad programming practice.

Andrus.

 

[Jon Skeet [C# MVP]]

Using statement forces deterministics dispose of dc object. Dispose consumes
additional CPU cycles which are not required.
So this may cause perfomance loss since

So I'm wondering why you use using statement with datacontext. Maybe bad
habit from using connection object ?
It seems to be bad programming practice.

Using a connection/context for only as long as you need it is *good*
practice rather than bad practice.

 

[Andrus]

Using a connection/context for only as long as you need it is *good*

practice rather than bad practice.

Jon,

context object closes connection automatically. No need to force connection
closing by using command.
Using command forces dispose to be called immediately. There is no any need
to call Dispose() immediately after using context object.
Dispose wastes CPU resources.

I really do'nt understand why wasting CPU time thus causing application
working more slowly when this is not needed is considered good practice.

Andrus.

 

[Jon Skeet [C# MVP]]

context object closes connection automatically. No need to force connection
closing by using command.

Which kind of context are you talking about? I don't believe a LINQ to
SQL context will close its connection automatically without calling
Dispose on it - at least not in a timely manner.

Using command forces dispose to be called immediately.

Absolutely - at exactly the right time!

There is no any need to call Dispose() immediately after using context
object.

You should, it's good practice to dispose of an object which implements
IDisposable when you know you've finished with it.

Dispose wastes CPU resources.

Show me an example where calling Dispose() imposes a *significant*
performance hit, and I'll concede your point.

In the meantime, it's very easy to come up with plenty of examples
where *not* calling Dispose has given people issues.

I really do'nt understand why wasting CPU time thus causing application
working more slowly when this is not needed is considered good practice.

See above.

 

[Marc Gravell]

So it is possible to use dynamic properties added using TypeDescriptor in

queries created at runtime ?

No; Expression is tightly coupled to MemberInfo (PropertyInfo,
FieldInfo, etc), not PropertyDescriptor. It will not work with
System.ComponentModel, at least not with the current implementation
(and I doubt this will change).

Double edged sword; at one level I like it, but I also see the
frustration.

Marc

 

[Marc Gravell]

Minor retraction:

Actually, they are subtly different at the IL level - compiler-generated
bings to the "get_{Foo}" MethodInfo, where-as dynamically binds to the
"{Foo}" PropertyInfo

I inspected the Expression object earlier (compiled vs dynamic), and
the MemberInfo was the same (ReferenceEquals) in both cases - so
although the IL looks different (via memberof(...)), it is the same.
But obviously compile-time lambdas provide more compile-time safety.

Marc

 

[Andrus]

Which kind of context are you talking about? I don't believe a LINQ to

SQL context will close its connection automatically without calling
Dispose on it - at least not in a timely manner.

I'm talking MS Linq to SQL.
I read in this newsgroup in Linq-WinForms thread that its Context will
release connection immediately after every db method call (Submit(),
retrieve etc) to connection pool.
So it is possible that context object can exist for a long time as it does
not leave connection open,
no need to dispose it immediately.

In the meantime, it's very easy to come up with plenty of examples
where *not* calling Dispose has given people issues.

I read Linq-SQL docs in MSDN and havent found any warning about this.


Can you point me any example where not disposing MS Linq-SQL Context object
can cause any issue ?


Andrus.

 

[Jon Skeet [C# MVP]]

I'm talking MS Linq to SQL.
I read in this newsgroup in Linq-WinForms thread that its Context will
release connection immediately after every db method call (Submit(),
retrieve etc) to connection pool.

Looking at the Connection property for DataContext, that looks like
it's true - but the connection can be manually opened too.

So it is possible that context object can exist for a long time as it does
not leave connection open,
no need to dispose it immediately.

See above.

I read Linq-SQL docs in MSDN and havent found any warning about this.

*Anything* implementing IDisposable should be disposed unless you
absolutely know that it doesn't need to be.

Can you point me any example where not disposing MS Linq-SQL Context object
can cause any issue ?

You may explicitly open the connection, and then forget to explicitly
close it. Disposing of the DataContext will close the connection.

In addition, if future providers (implementations of IProvider) hold
other resources, it's reasonable to assume that disposing the
DataContext will clear them up. It's just good practice to dispose of
things implementing IDisposable.

Your argument about wasting CPU time doesn't hold water - can you even
*measure* the amount of time which is "wasted"? Compare that amount of
time with the time it takes to establish a connection to the database
in the first place, and you'll see how completely insignificant it is.