CODEWITHSUNDEEP
asp.net-mvc-3linqentity-framework
Tun
Tun

Reputation: 912

String extension method in Linq query

How to use string extension method in linq query:

public NewsType GetNewsType(string name)
{
   var newsType = db.NewsTypes.FirstOrDefault(x => x.Name.ToFriendlyUrl() ==  
                                              name.ToFriendlyUrl());
   return newsType;
}

Above query x.Name.ToFriendlyUrl() is not allowed at the minute. Is anyone know how to achieve with it.

Upvotes: 5

Views: 1502

Answers (4)

xanatos
xanatos

Reputation: 111850

This

var newsType = db.NewsTypes.FirstOrDefault(
          x => x.Name.ToFriendlyUrl() == name.ToFriendlyUrl());

can't be done in Entity Framework. ToFriendlyUrl is an extension method. It's something that is in the "client" computer. The query will be executed on the SQL server. The SQL server doesn't have a ToFriendlyUrl function.

The "standard" solution is to save in a second column named FriendlyName a precalculated version of the ToFriendlyUrl(), so your query becomes:

var friendlyName = name.ToFriendlyUrl();
var newsType = db.NewsTypes.FirstOrDefault(
          x => x.FriendlyName == friendlyName);

Upvotes: 1

SWeko
SWeko

Reputation: 30882

Extension methods are indeed allowed in LINQ queries, moreover the LINQ methods themselves are implemented as extension methods.

It's quite another issue however, to use extension methods (or most other methods) in LINQ-to-SQL or LINQ-to-Entities queries. Those queries are not actually run in the C# code, but they are treated like expressions that are translated to SQL. I.e.

db.News.Where(x => x.Published).Select(x => x.Name)

is translated to the SQL Statement

Select Name 
From News
Where Published = 1

and it's results are returned to the C# code.

Since there is not way to transfer the ToFriendlyUrl() method to SQL, your code throws an error.

You have basically, two solutions/workarounds. One is to transform the call to a form could be translated into SQL, e.g. if the ToFriendlyUrl() method was just:

public static string ToFriendlyURL(this string value)
{
    return value.ToLower();
}

you can inline that code in the LINQ call, and that would work. If however, the methods is more complex, than your only solution is to just fetch the data from the base and then process it on the C# side:

var newsTypeQuery = db.NewsTypes.Where(x => // other conditions, if any);
var newsTypes = newsTypes.ToList(); //forces execution of the query
                                    // the result is now a C# list
var newsType = newsTypes.FirstOrDefault(x => 
                                 x.Name.ToFriendlyUrl() == name.ToFriendlyUrl());

Upvotes: 3

Martin Liversage
Martin Liversage

Reputation: 106826

Assuming the NewsTypes is an IQueryable this is a result of Entity Framework not being able to convert you extension method into SQL (how should it?). Unless you can rewrite your predicate into something that Entity Framework can translate into SQL you will have to perform the query client side:

public NewsType GetNewsType(string name)
{
   var newsType = db.NewsTypes.AsEnumerable().FirstOrDefault(x => x.Name.ToFriendlyUrl() == name.ToFriendlyUrl());
   return newsType;
}

Notice how AsEnumerable() has been added before FirstOrDefault. Unfortunately this may pull all the rows returned by NewsTypes from the server to client and thus may be quite costly.

Upvotes: 1

Jagadesh
Jagadesh

Reputation: 374

Instead try like this

public NewsType GetNewsType(string name)
{
   var newsType = db.NewsTypes.FirstOrDefault(x => x.Name == name).ToFriendlyUrl();
   return newsType;
}

Upvotes: 0

Related Questions