Is it possible to create custom methods in entity classes in linq to sql

Question

I have a table Site in SQL with (amongst others) three properties idReviewer1, idReviewer2, idReviewer3. Now I would like to create a methods on the entity class of Site to check if a user is a reviewer:

partial class Site
{
    public bool IsReviewer(int idUser)
    {
        return idReviewer1 == idUser || idReviewer2 == idUser || idReviewer3 == idUser;
    }
}

and I use it like this:

return from s in db.Sites
       where s.IsReviewer(user)
       select s;

However, Linq to SQL doesn't know how to translate this to SQL. I get the following error message:

Method 'Boolean IsReviewer(Int32)' has no supported translation to SQL.

I'd rather not write this:

return from s in db.Sites
       where idReviewer1 == idUser || idReviewer2 == idUser || idReviewer3 == idUser
       select s;

Is there any way to put this functionality in one place, without resorting to SQL?

Enrico Campidoglio · Answer 1 · 2010-06-11T13:22:53.403

You could implement that method as a stored procedure, which you then add to your LINQ to SQL model as a function. Then you would be able to do something like:

ISingleResult<Site> sites = db.SelectSitesByReviewer(userId);

Alternatively you could implement it as a user-defined function (UDF), which will allow you to use it in a LINQ query:

IEnumerable<Site> sites = from site in db.Sites
                          where db.IsReviewer(site.Id, userId)
                          select site;

However I don't see anything wrong in defining a LINQ query like the one you mention in your question. Using a Stored Procedure or a UDF in this case doesn't really buy you much and requires you to move some logic away from the application into the database, which may or may not comply with your architecture.

Related resources

The UDF would also need a siteid: where db.IsReviewer(site.siteId, userId) The advantage of putting this in one place is when I want to add another reviewer (idReviewer1..idReviewer4). But it's not a biggie. I'm just learning... — doekman, Jun 11 '10 at 11:46

score 2 · Answer 2 · answered Jun 10 '10 at 08:25

2

I think you're possibly passing a My.DAL.User, not an int, into your method based on the error "'Boolean IsReviewer(My.DAL.User)'". Try (where s.IsReviewer(user.Id)) and see if that works?

answered Jun 10 '10 at 08:25

Matt Mitchell

40,943
35
118
185

Boolean IsReviewer(My.DAL.User)' is the error message. The My.DAL.User-part is the namespace. – doekman Jun 10 '10 at 09:04
I'm inclined to agree with this answer. It looks as though you're passing a User type to a function that expects an int, and the compiler error is returning an unhelpful error message. – Josh Smeaton Jun 10 '10 at 09:18
@Josh: You are correct. I made a copy/paste error. Fixed the error message. – doekman Jun 10 '10 at 10:19

score 2 · Answer 3 · answered Jun 10 '10 at 08:55

2

Unfortunately probably know, unless You change (extend) linq2SQL query provider. The provider encounters a method which indeed is not known to him and He has no possible way of knowing how to translate the method to sql. Even though for this case it is simple in general it is impossible (even dangerous). But You can do it the other way. You can make a delagate (or even better a compiled query) that takes a Site and does the condition and then use the method syntax with

Func<Site,int,Bool> isRevier = (site, idUser) => site.idReviewer1 == idUser || site.idReviewer2 == idUser || site.idReviewer3 == idUser;
.Where(IsReviewer)

answered Jun 10 '10 at 08:55

luckyluke

1,553
9
16

It's generally a good way to use Expressions (and delegates) when working with Linq (all aspects) – luckyluke Jun 10 '10 at 08:56
Hmm, now I get "Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.". In the query syntax, I use: where isReviewer(ouc.OrganizationalUnit.Site, idUser) – doekman Jun 10 '10 at 10:26
Sorry I missed it. You shoudl return an Expression that is Expression >( and then use it in where... That things happen when I do not have visual stuido:) Query provider translates the expressions not funcs (why transalte funcs if You'e got expressions) .. sorry for that – luckyluke Jun 11 '10 at 14:30

score 2 · Accepted Answer · answered Jun 11 '10 at 02:45

2

Use a Func rather than a method invocation.

partial class Site  
{  
    public static Func<Site, bool> IsReviewer(int idUser)  
    {  
        return (s => s.idReviewer1 == idUser 
          || s.idReviewer2 == idUser 
          || s.idReviewer3 == idUser);  
    }  
}  

return db.Sites.Where(Site.IsReviewer());

answered Jun 11 '10 at 02:45

Matt Mitchell

40,943
35
118
185

1

Also a nice one (this one actually works). But I think you can't use this solution with the query syntax... BTW, the last statement needs an idUser: return db.Sites.Where(Site.IsReviewer(1)); – doekman Jun 11 '10 at 12:09
Yeah couldn't manage to get query syntax support on this one - good catch on the int too. – Matt Mitchell Jun 14 '10 at 13:02
You should declare Expression> for working in IQueryable-scope. Isn't it? – vladimir Sep 20 '12 at 07:42

Is it possible to create custom methods in entity classes in linq to sql

4 Answers4