Linq To SQL Without Explicit Foreign Key Relations

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): alt text http://i43.tinypic.com/ohl00l.jpg

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

After a few tests, I'm pretty sure the FK relationships are required in the DB regardless of whatever associations are created in Linq-to-SQL. i.e. if you don't have them explicitly set in the DB, then you will have to do a join manually.

Answer 3

Your query looks correct and should return a query result set of Case objects.

So... what's the problem?

(Edit) My problem being that CaseInfo is not available under Cases... i.e. c.CaseInfo doesn't exist where I'm assuming it would be if there were explicit primary/foreign key relationships.

What do you mean by "not available"? If you created the association in the designer as you say you did, then the query should generate SQL something along the lines of

SELECT [columns] 
FROM Case INNER JOIN CaseInfo 
   ON Case.CaseID = CaseInfo.CaseID
WHERE CaseInfo.CaseInfoMeta = 'some value'

Have you debugged your linq query to get the SQL generated yet? What does it return?

Answer 4

Is this c#? I think you need == instead of = on this line:

where c.CaseInfo.CaseInfoMeta = "some value"

should read

where c.CaseInfo.CaseInfoMeta == "some value"

Answer 5

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.

Answer 6

Couple of things you might want to try:

Check the properties of the association. Make sure that the Parent property was created as Public. It does this by default, but something may have changed.

Since you're not getting CaseInfo on C, try typing it the other direction to see if you get ci.Case with intellisense.

Delete and recreate the association all together.

There's something very basic going wrong if the child members are not showing up. It might be best to delete the dbml and recreate the whole thing.

If all else fails, switch to NHibernate. :)