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?

Answer 1

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?

Answer 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)

Answer 3

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

• Stored Procedures (LINQ to SQL)
• User-Defined Functions (LINQ to SQL)
• How to: Call User-Defined Functions Inline (LINQ to SQL)

Answer 4

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());