LINQ to SQL Lookup table? Join perhaps? I'm lost... ;/

Question

Not sure if this is called a lookup table... Here's a screenshot of the schema: http://apoads.com/db-schema.png

What I want to do is join the Ad and MilBase table where the MilBase.BaseID is a given value. In the result, I'd like to be able to access the data like so: ad.MilBase.BaseName, etc...

I just can't seem to wrap my mind around this right now... ;/

Answer 1

Your problem is that your schema has the semantics of a many-to-many relationship between Ad and MilBase and as such the really desired way you would be wanting to do this in LINQ would be ad.Milbases which would then hold a Collection of Milbases.

The problem is not made any better by the fact that LINQ To SQL does not support many-to-many relationships directly, and in reality (assuming that your lookup table only defines a one-to-one or one-to-many relationship) you'd have to do something like

ad.Adbases.Single().MilBase

Of course assuming there will always be one - if that's not the case, then you've got some more complicated things ahead of you.

Of course a join is also always possible too - either way - if this is not the information you were looking for, could you clarify the relationship between Ad and Milbase? (many-to-many, etc?). Also - if this is not a many-to-many and you are able to do so, I would really change this to be a foreign key in either Milbase or the Ad table.

EDIT: In response to your comment,

You could do something like

var query = from a in db.ad
            select new {
               a.property1,
               a.property2,
               ...
               Milbases = a.Adbases.Select(s => s.Milbase)
            };

Obviously that code won't compile, but it should give you a rough idea - also I'm certain it can be done in better ways, but something like that should work.