question about LINQ

Question

hi all. I have Three Tables in SqlServer (equivalent to image in link below in SQL Server)

http://www.mojoimage.com/free-image-hosting-view-06.php?id=2Untitled-2.gif

and when i use LINQ to Sql i get three classes in dbml file:

class Item
{
Guid ItemId;
int ItemSize;
}

class Video
{
Guid RecordId;
Guid ItemId;
string Resolution;
}

class Audio
{
Guid RecordId;
Guid ItemId;
int Duration;
}

then i create this three interfaces:

Interface IItem
{
Guid ItemId;
int ItemSize;
}

Interface IVideo
{
string Resolution;
}

Interface IAudio
{
int Duration;
}

and a class named VideoItem that implements both "Video" interface and "Item" interface:

class VideoItem:IItem,IVideo
{
public Guid ItemId;
public int ItemSize;
public string Resolution;
}

and a class named AudioItem that implements both "Audio" interface and "Item" interface.

class AudioItem :IItem,IVideo
{
public Guid ItemId;
public int ItemSize;
public int Duration;
}

now i want to use LINQ to query VideoItem instances from my tables (those are in both Item table and Video table).

do i need to create custom LINQ provider?

Answer 1

from ai in Audio
where ai.XXXX == whatever
select new AudioItem
    {
        ItemId = ai.ItemId,
        ItemSize = ai.Item.ItemSize,
        Duration = ai.DUration
    }

This assume that you have a foreign-key releationship set up between Item & Audio based on ItemId. If not, you'll have to go with:

from ai in Audio
join i in Item on ai.ItemId equal i.ItemiD
where ai.XXXX == whatever
select new AudioItem
    {
        ItemId = ai.ItemId,
        ItemSize = i.ItemSize,
        Duration = ai.DUration
    }

UPDATE: You can create a List of AUdioItems like this:

var listOfAudioItems =
(from ai in Audio
where ai.XXXX == whatever
select new AudioItem
    {
        ItemId = ai.ItemId,
        ItemSize = ai.Item.ItemSize,
        Duration = ai.DUration
    }).ToList();

Once you have that, you can use it in a LINQ state as a single table:

 from ai in listOfAudioItems
 where ai.Duration > 5 && ai.ItemSize == 10
 select ai;

Answer 2

Hey,

The solution provided by @James will be a lot easier than writing a custom LINQ provider. You can select a join between the two entities by using a LINQ query joining the two tables and selecting the results as an AudioItem or VideoItem respectively.