Creating LINQ to SQL Record Properties that Point to Foreign Key Fields?

Question

In my LINQ to SQL generated db class, I have a table with a foreign key reference to a field that has useful date information. A query might look something like this:

var query = from a in db.TableA
            join b in TableB on a.FK_B_Id equals b.Id
            where b.Date.Value <= DateTime.Today
            select a;

...or more simplified:

var query = from a in db.TableA
            where a.FK_B.Date.Value <= DateTime.Today
            select a;

The "Date" field in Table B is nullable, but there is a constraint in the database that specifies that TableA records can only link to a TableB record where Date is not null. So to make it clearer to anyone writing LINQ queries, it would make more sense to query like so:

var query = from a in db.TableA
            where a.Date <= DateTime.Today
            select a;

Is it possible to create a "Date" property in the TableA partial class that would return the Date property of the TableB foreign key reference (and have it be able to be used in queries)? Is this more of an Entity Framework type thing to do, or can LINQ to SQL handle this type of thing? Thanks in advance.

Answer 1

I don't know about Linq to SQL, but Entity Framework has something called Navigation Properties, which allow you to query associated entities for a given entity.

Answer 2

To answer your question, yes that is possible. The only caveat is that your partial value must be something that can translate to sql. You run into this problem in cases where you might have logic in your partial value that sql has no equivalent to.