mysql - Convert sql query with a join on a subselect to a linq statement -

i trying convert following sql query linq statement

select t.*    (         select unique_id, max(version) mversion            test         group unique_id     ) m inner join     test t  on m.unique_id = t.unique_id , m.mversion = t.version 

linq statement

var testalt = (from altt in cs.test group altt altt.unique_id g join bp in cs.alerts on g.firstordefault().unique_id equals bp.unique_id select new abcbe { abcname= bp.name, number = bp.number, unique_id =  g.key, version = g.max(x=>x.version) }); 

i getting error of clause. please help

sql fiddle

this not easy straight forward conversion can accomplish same thing using linq method syntax. first query executed expression tree, joining expression tree grouping against cs.alerts. combines expression tree cs.test query expression tree of cs.alerts join 2 expression trees.

the expression tree evaluated build query , execute said query upon enumeration. enumeration in case tolist() call gets result enumeration execute query.

var query1 = cs.test.groupby(x => x.unique_id); var joinresult = cs.alerts.join(query1,     alert => new { id = alert.unique_id, version = alert.version },    test => new { id = test.key, version = test.max(y => y.version },     (alert, test) => new abcbe {        abcname = alert.name,        number = alert.number,        unique_id = test.key,        version = test.max(y => y.version)     }).tolist(); 

because query1 still iqueryable , using cs.alerts (which i'm guessing cs data context) should join , build query execute upon tolist() enumeration.