How to create a custom property in a Linq-to-SQL entity class?

Question

I have two tables Studies and Series. Series are FK'd back to Studies so one Study contains a variable number of Series. Each Series item has a Deleted column indicating it has been logically deleted from the database.

I am trying to implement a Deleted property in the Study class that returns true only if all the contained Series are deleted. I am using O/R Designer generated classes, so I added the following to the user modifiable partial class for the Study type:

public bool Deleted
{
    get
    {
        var nonDeletedSeries = from s in Series
                               where !s.Deleted
                               select s;
        return nonDeletedSeries.Count() == 0;
    }
    set
    {
        foreach (var series in Series)
        {
            series.Deleted = value;
        }
    }
}

This gives an exception "The member 'PiccoloDatabase.Study.Deleted' has no supported translation to SQL." when this simple query is executed that invokes get:

IQueryable<Study> dataQuery = dbCtxt.Studies;
dataQuery = dataQuery.Where((s) => !s.Deleted);
foreach (var study in dataQuery)
{
   ...
}

Based on this http://www.foliotek.com/devblog/using-custom-properties-inside-linq-to-sql-queries/, I tried the following approach:

static Expression<Func<Study, bool>> DeletedExpr = t => false;
public bool Deleted
{
    get
    {
        var nameFunc = DeletedExpr.Compile();
        return nameFunc(this);
    }
    set
    {  ... same as before
    }
}

I get the same exception when a query is run that there is no supported translation to SQL. ( The logic of the lambda expression is irrelevant yet - just trying to get past the exception.)

Am I missing some fundamental property or something to allow translation to SQL? I've read most of the posts on SO about this exception, but nothing seems to fit my case exactly.

Where is the error occurring? In the set or the get? something like "there's an error in the following 20 lines of code, help me" does't give us much to go on... The exact detail of the exception would also be helful — Peter Ritchie, Jul 26 '12 at 18:14
I'm not clear where there's a custom property used within a linq-to-sql query. You've detailed you've added a Deleted property to Study; but the query you've detailed uses only Series. — Peter Ritchie, Jul 26 '12 at 18:16
@PeterRitchie - updated question to provide mssing info. Thanks. — Steve Fallows, Jul 26 '12 at 18:55
You can't have it this way. The compiler converts the lambda into an Expression> and LINQ-to-SQL then tries to map Study.Delete to a field. Either do dataQuery.ToList().Where((s) => !s.Deleted) or use an extension. Also, using a compiled predicate this way won't help you either. All the magic happens when lambdas or linq statements get converted into expressions and later are mapped. — Ruslan, Jul 26 '12 at 19:10
possible duplicate of [How to use a custom property in a LINQ-to-Entities query](http://stackoverflow.com/questions/2241643/how-to-use-a-custom-property-in-a-linq-to-entities-query) — Hannele, Mar 12 '14 at 21:18

score 1 · Accepted Answer · answered Jul 26 '12 at 17:21

I believe the point of LINQ-to-SQL is that your entities are mapped for you and must have correlations in the database. It appears that you are trying to mix the LINQ-to-Objects and LINQ-to-SQL.

If the Series table has a Deleted field in the database, and the Study table does not but you would like to translate logical Study.Deleted into SQL, then extension would be a way to go.

public static class StudyExtensions
{
    public static IQueryable<study> AllDeleted(this IQueryable<study> studies)
    {
        return studies.Where(study => !study.series.Any(series => !series.deleted));
    }
}

class Program
{
    public static void Main()
    {
        DBDataContext db = new DBDataContext();
        db.Log = Console.Out;

        var deletedStudies = 
            from study in db.studies.AllDeleted()
            select study;

        foreach (var study in deletedStudies)
        {
            Console.WriteLine(study.name);
        }
    }
}

This maps your "deleted study" expression into SQL:

SELECT t0.study_id, t0.name
FROM study AS t0
WHERE NOT EXISTS(
    SELECT NULL AS EMPTY
    FROM series AS t1
    WHERE (NOT (t1.deleted = 1)) AND (t1.fk_study_id = t0.study_id)
)

Alternatively you could build actual expressions and inject them into your query, but that is an overkill.

If however, neither Series nor Study has the Deleted field in the database, but only in memory, then you need to first convert your query to IEnumerable and only then access the Deleted property. However doing so would transfer records into memory before applying the predicate and could potentially be expensive. I.e.

var deletedStudies = 
    from study in db.studies.ToList()
    where study.Deleted
    select study;

foreach (var study in deletedStudies)
{
    Console.WriteLine(study.name);
}

score 0 · Answer 2 · answered Mar 12 '14 at 21:11

When you make your query, you will want to use the statically defined Expression, not the property.

Effectively, instead of:

dataQuery = dataQuery.Where((s) => !s.Deleted);

Whenever you are making a Linq to SQL query, you will instead want to use:

dataQuery = dataQuery.Where(DeletedExpr);

Note that this will require that you can see DeletedExpr from dataQuery, so you will either need to move it out of your class, or expose it (i.e. make it public, in which case you would access it via the class definition: Series.DeletedExpr).

Also, an Expression is limited in that it cannot have a function body. So, DeletedExpr might look something like:

public static Expression<Func<Study, bool>> DeletedExpr = s => s.Series.Any(se => se.Deleted);

The property is added simply for convenience, so that you can also use it as a part of your code objects without needing to duplicate the code, i.e.

var s = new Study();
if (s.Deleted)
    ...

How to create a custom property in a Linq-to-SQL entity class?

2 Answers2