Arnab Arnab - 7 months ago 57
C# Question

left non equi join linq

I have a list of objects in table tblB which is retrieved from db in following format.

public class playdata
{
public string consumerid { get; set; }
public string play_time { get; set; }
public string genre{ get; set; }
.
.
.
public int mycounter{ get; set; }
}


I have a table tblA which has a column colA which has int from 0 to 1000.

I wish to create a linq query similar to sql as below..

Select x.i as numindex, y.consumerid,y.play_time,y.genre
From
(Select colA as i from tblA) x
left join
(
Select consumerid, play_time,genre,mycounter from tblB
)y on y.mycounter > x.i


I tried the following unsuccessfully..

I came to find that Enumerable.Range(0, 1001) generated a numeric series so no need to get that table data..

List<playdata> plays = .....



var q= (from s in Enumerable.Range(0, 1001)
join p in plays on s < p.mycounter into t
from rt in t.DefaultIfEmpty() select new{
numindex=s,
consumerid=p.consumerid,
play_time =p.play_time,
genre=p.genre
}).ToList();


I see two errors in second line..

p is not in scope of left side of equals .. I have also tried..p.mycounter>s with same result.

The other error is in into where it shows.. expected contextual keyword equals

The errors go away if I change the second line to..

join p in plays on s equals p.mycounter into t


All help is sincerely appreciated.

Thanks

Answer

You have to use the alternative way of representing left outer join in LINQ (shown in Joins - Inner, Left, Right, Cross and Full Outer Joins):

var q =
    (from i in Enumerable.Range(0, 1001)
     from p in plays.Where(x => x.mycounter > i).DefaultIfEmpty()
     select new
     {
         numindex = i,
         consumerid = p?.consumerid,
         play_time = p?.play_time,
         genre = p?.genre
     }).ToList();

Note that in LINQ to Objects you have to account for right side of the left outer join returning null when there is no matching element, otherwise you'll get NullReferenceException.