Linq To SQL Without Explicit Foreign Key Relationships

Question

I am working with a few legacy tables that have relationships, but those relationships haven't been explicitly set as primary/foreign keys. I created a .dbml file using "Linq To Sql Classes" and established the proper Case.CaseID = CaseInfo.CaseID association. My resulting class is CasesDataContext.

My Tables (One to many):

Case
------------------
CaseID (int not null)
MetaColumn1 (varchar)
MetaColumn2 (varchar)
MetaColumn3 (varchar)
...


CaseInfo
------------------
CaseInfoID (int)
CaseID (int nulls allowed)
CaseInfoMeta (varchar)
...

I'm new to LinqToSQL and am having trouble doing..

CasesDataContext db = new CasesDataContext();
var Cases = from c in db.Cases
            where c.CaseInfo.CaseInfoMeta == "some value"
            select c;

(Edit) My problem being that CaseInfo or CaseInfos is not available as a member of Cases.

I heard from a colleague that I might try ADO.Net Entity Data Model to create my Data Context class, but haven't tried that yet and wanted to see if I'd be wasting my time or should I go another route. Any tips, links, help would be most appreciated.

Answer 1

Go back to the designer and check the relation is set up correctly. Here is one real life example, with BillStateMasters have "CustomerMasters1" property (customers for the state):

Ps. naming is being cleaned up ...

Update 1: You also need to make sure both tables have a primary defined. If the primary key isn't defined on the database (and can't be defined for whatever reason), make sure to define them in the designer. Open the column's properties, and set it as primary key. That said, entity tracking also won't work if you haven't a primary key for the entity, which for deletes means it silently doesn't updates the entity. So, make sure to review all entities and to have them all with a primary key (as I said, if it can't be on the db, then on the designer).

Answer 2

CasesDataContext db = new CasesDataContext();
var Cases = from c in db.Cases
            join ci in db.CaseInfo on
            ci.ID equals c.InfoID
            where ci.CaseInfoMeta == "some value"
            select new {CASE=c, INFO=ci};

my "join" linq is a bit rusty, but the above should get close to what you're after.