Linq To Sql Select Inside An Inner Join

I am a beginner in linq to sql, I am wondering what is the syntax in for a select inside a inner join : inner join ( select CCL_TMA_ID as SecurityIdMax ,

Solution 1:

I've done a query for you below with a few comments explaining some of the features. Note that you can't do a multiple condition join based on <= such as

on t0.CCL_TMA_ID = SecurityIdMax
                             and t0.CCL_DATE <= DateMax
                             and t0.CCL_DATE >= DateMax-10

you'd have to join on the first condition and then filter them out with a where afterwards

eg

Datetime d = Datetime.Now;
Datetime lastYear = d.AddYears(-1);
var q = from t0 in db.usrCOURSCLOTURE
        join t1 in db.usrCOURSCLOTURE.where(z => z.CCL_DONNEE.HasValue) 
        onnew {a = t0.CCL_DEV_DONNEE, b = t0.CCL_DATE} equalsnew {a = t1.CCL_TMA_ID, b = t1.CCL_DATE}
            // the above is how to do a join on multiple conditionsjoin t2 in (from x0 in db.usrCOURSCLOTURE.where(z => z.CCL_DONNEE.HasValue && z.CCL_DATE < d)
            .GroupBy(z => z.CCL_TMA_ID)
            selectnew {SecurityIdMax = x0.Key, DateMax = x0.Max(z => z.CCL_DATE)}
            //this is how you get your groupby subquery
        )
        on t0.CCL_TMA_ID equals t2.SecurityIdMax
        where
          t0.CCL_DATE  > lastYear
          && t0.CCL_DATE <= t2.DateMax
          && t0.CCL_DATE >= SqlFunctions.DateAdd("DAY", -10, t2.DateMax) //nb not sure on the interval - correct this!selectnew {SecurityId = t0.CCL_TMA_ID,
                    Date = t0.CCL_DATE,
                    Price = t0.CCL_DONNEE,
                    CurrencyPrice = t1.CCL_DONNEE};

Also note that the "SqlFunctions" class is in the namespace System.Data.Objects.SqlClient in the System.Data.Entity assembly.

Solution 2:

By enclosing statement into brackets you create a subset of data which in your case will be selection from dbo.usrCOURSCLOTURE grouped by CCL_TMA_ID column.

To make it clearer lets put it in different way:

@subsetOfData = select CCL_TMA_ID as SecurityIdMax, max(CCL_DATE) as DateMax
                      from   dbo.usrCOURSCLOTURE
                     where  CCL_DONNEE is not null and CCL_DATE <= @d
                  group by CCL_TMA_ID

and then

select t0.CCL_TMA_ID as SecurityId ,
        t0.CCL_DATE as Date ,
        t0.CCL_DONNEE as Price ,
        t1.CCL_DONNEE as CurrencyPrice
 from   dbo.usrCOURSCLOTURE as t0
        inner join dbo.usrCOURSCLOTURE as t1 on t0.CCL_DEV_DONNEE = t1.CCL_TMA_ID
                                                and t0.CCL_DATE = t1.CCL_DATE
                                                and t1.CCL_DONNEE isnotnull

        inner join @subsetOfData as cMax on t0.CCL_TMA_ID = SecurityIdMax
                             and t0.CCL_DATE <= DateMax
                             and t0.CCL_DATE >= DateMax-10where t0.CCL_DATE > dateadd(year,-1,@d)