linq to sql linking objects by multiple ID fields

Question

Im working on a project at work which is pretty much a commissioning manager and i cant seem to figure out how to link the Items table properly in linq to sql.

I have a package detail table that has (among other things)

DepartmentID, CategoryID, ItemID

And the Items table (is actually a database view as its from a different database and is read only in this app) also have these 3 fields but when i add an association with these 3 fields it doesnt add that as a property object ot the PackageDetail class

am i doing something wrong with the association? all the single ones im doing work fine...

Answer 1

have marked those ID fields as Primary keys? Make sure you assign the necessary columns as primary keys and this should work fine. Hope it helps

Answer 2

Ensure the keys are correctly setup with primary key and foreign key relationship. If that still doesn't work, could you consider adding a new key column, rather than relying on composite key?

Last option with LINQ to SQL is usually manually updating the DBML with an XML editor. A normal single key relationship appears as follows:

<Association Name="Schedule_Profile" Member="Schedule" ThisKey="ScheduleID" Type="Schedule" IsForeignKey="true" />

Suggest you try creating the element yourself, and try setting ThisKey to a csv list of columns. The OtherKey attribute may also be of interest.

Answer 3

I don't believe that Linq-to-SQL can properly model an association with a composite key:

http://stackoverflow.com/questions/647146/is-it-beneficial-to-use-multicolumn-composite-primary-keys-when-using-linq-to-s

However, you can still load objects with composite keys in a Linq-to-SQL query using an anonymous object as the (single) key:

http://msdn.microsoft.com/en-us/library/bb399391.aspx

Answer 4

It looks like you could just use ItemId and ignore the other 2 since that is the most specific qualifier - in other words, Department and Category are fully determined by itemId.

Answer 5

Did you mean a query like this.

var result = from table in dbContext.table1 join table2 in dbContext.table2 join new { table.DepartmentID, table.CategoryID, table.ItemID} equals new {table2.DepartmentID, table2.CategoryID, table2.ItemID}
select table;