CODEWITHSUNDEEP
c#sqllinqasp.net-4.0
Puppy
Puppy

Reputation: 146910

Accessing foreign keys through LINQ

I have a setup on SQL Server 2008. I've got three tables. One has a string identifier as a primary key. The second table holds indices into an attribute table. The third simply holds foreign keys into both tables- so that the attributes themselves aren't held in the first table but are instead referred to. Apparently this is common in database normalization, although it is still insane because I know that, since the key is a string, it would take a maximum of 1 attribute per 30 first table room entries to yield a space benefit, let alone the time and complexity problems.

How can I write a LINQ to SQL query to only return values from the first table, such that they hold only specific attributes, as defined in the list in the second table? I attempted to use a Join or GroupJoin, but apparently SQL Server 2008 cannot use a Tuple as the return value.

Upvotes: 0

Views: 796

Answers (3)

Pop Catalin
Pop Catalin

Reputation: 62920

"I attempted to use a Join or GroupJoin, but apparently SQL Server 2008 cannot use a Tuple as the return value".

You can use anonymous types instead of Tuples which are supported by Linq2SQL.

IE:

from x in source group x by new {x.Field1, x.Field2}

Upvotes: 2

StriplingWarrior
StriplingWarrior

Reputation: 156459

I'm not quite clear what you're asking for. Some code might help. Are you looking for something like this?

var q = from i in ctx.Items
        select new 
        {
            i.ItemId,
            i.ItemTitle,
            Attributes = from map in i.AttributeMaps
                         select map.Attribute
        };

Upvotes: 2

RThomas
RThomas

Reputation: 10882

I use this page all the time for figuring out complex linq queries when I know the sql approach I want to use.

VB http://msdn.microsoft.com/en-us/vbasic/bb688085

C# http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

If you know how to write the sql query to get the data you want then this will show you how to get the same result translating it into linq syntax.

Upvotes: 0

Related Questions