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

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 2

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 3

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 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

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.