Transforming T-SQL Query into C# LINQ with joins on multiple conditions and also grouping on multiple conditions -

first want hello, i'm new site ;-)

my problem transform following sql-query c# linq-query.

( have searched hard existing answer i'm not able combine solution joins on multiple conditions , grouping / counting ! )

the sql-query :

declare @datestart datetime declare @dateend datetime  set @datestart = '01.04.2014' set @dateend = '30.04.2014'  select md1.value [controller],md2.value [action], count(md2.value)   [accesscount], max(re.timestamp) [lastaccess] recorderentries re inner join messagedataentries md1 on re.id = md1.recorderentry_id , md1.position =  0 inner join messagedataentries md2 on re.id = md2.recorderentry_id , md2.position = 1 re.timestamp >= @datestart , re.timestamp <= @dateend  , re.messageid in ('id-01','id-02' ) group md1.value,md2.value order [accesscount] desc  

any suggestions welcome ...

what have far :

var _recorderactioncalls = (from r in _dbcontext.recorderentries                                     join m1 in _dbcontext.messagedataentries on                                      new {                                         = r.id,                                         b = 0                                      } equals new {                                         = m1.id,                                         b = m1.position                                      }                                     join m2 in _dbcontext.messagedataentries on                                                        new {                                                           = r.id,                                                           b = 0                                                        } equals new {                                                           = m2.id,                                                           b = m2.position                                                        }                                     r.timestamp >= startdate & r.timestamp <=     enddate & (r.messageid == "varec_100_01" | r.messageid == "vaauth-100.01")                                     group r new { md1 = m1.value, md2 = m2.value }    r1                                     select new { controller = r1.key.md1, action =    r1.key.md2, count = r1.key.md2.count() }).tolist(); 

but throws exception ( translated german ) :

dbexpressionbinding requires input expression listing result type ...

update : headache ... ;-)

i found solution problem :

var _recorderactioncalls = _dbcontext.recorderentries                                  .where(r => r.timestamp >= startdate & r.timestamp <=     enddate & (r.messageid == "varec_100_01" | r.messageid == "vaauth-100.01"))                                  .groupby(g => new { key1 =    g.messagedata.firstordefault(md1 => md1.position == 0).value, key2 = g.messagedata.firstordefault(md2 => md2.position == 1).value })                                  .select(s => new {                                     controlleraction = s.key.key1 + " - " + s.key.key2,                                     value = s.count(),                                     last = s.max(d => d.timestamp)                                  }).tolist(); 

with syntax works me. thank thinking me :-)

something that:

list<string> messageidlist = new list<string> { "id-01", "id-02" };  re in recorderentries md1 in messagedataentries md2 in messagedataentries re.id = md1.recorderentry_id && md1.position == 0 re.id = md2.recorderentry_id && md2.position == 1 idlist.contains(re.messageid) let joined = new { re, md1, md2 } group joined new { controller = joined.md1.value, action = joined.md2.value }     grouped select new {      controller = grouped.key.controller,      action = grouped.key.action,          accesscount = grouped.where(x => x.md2.value != null).count(),      lastaccess = grouped.max(x => x.re.timestamp) }