LINQ self join in ASP.NET MVC3

Question

I have a situation where I need to do a self join on a table in LINQ. The table consists of fields ItemsID, Title, SeriesTitle, and many other. An item can be either a series or members and I can tell that by looking into ItemId which has "S" or "M" letters on it. I need to retrieve all records that are member of a series with ItemId "S117". I can do this in simple SQL by the code below,

select  i.Series_Title, i.Item_ID, i2.Item_ID as Member_ID, 
        i2.Title as Member_Title, i2.Series_Title as Member_Series_Title 
        from Items i join Items i2 on i.Series_Title = i2.Series_Title
        where i.Item_ID = "S117"

Now, I translated this query in LINQ which goes as

items = _dataContext.Items.AsQueryable();
items = from series in items 
        join members in items on series.Series_Title.ToLower() 
        equals members.Series_Title.ToLower()
        where series.Item_ID.ToLower().Equals(itemId)
        select series;

The last line of this query select series will only retrieve series but not members and I need members also. I am using MVC3 Razor view where I have to display almost all fields so I am not using select new {....} Even when I tried to use select new {series, members}, I got this exception -

Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.LinQ.IQueryable<My.App.models.Items>' An explicit conversion exist.

Any suggestions would be highly appreciated.

items is established at compile time as an IQueryable<Items> - trying to replace it with anything else will fail. You could select into a new collection, rather than replace your existing ones. — GalacticCowboy
– GalacticCowboy, Commented May 27, 2011 at 16:53

Meysam · Accepted Answer · 2011-06-19 13:40:14Z

1

Try this:

var items1 = _dataContext.Items.AsQueryable();
var items2 = from series in items1 
        join members in items1 on series.Series_Title
        equals members.Series_Title
        where series.Item_ID== 'S117'
        select series;

answered Jun 19, 2011 at 13:40

Meysam

5916 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LINQ self join in ASP.NET MVC3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related