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.

Answer 3

Is the association set to One to One or One to Many? If you have the association set to One to Many, then what you have is an EntitySet, not an EntityRef and you'll need to use a where clause on the dependent set to get the correct value. I suspect that you want a One to One relationship, which is not the default. Try changing it to One to One and see if you can construct the query.

Note: I'm just guessing because you haven't actually told us what the "trouble" actually is.

Answer 4

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 5

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. :)

Answer 6

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 7

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.