-1

I have a query here and its working in SQL, but I am using LINQ and I don't know how to convert it. Can anyone help me?

This is my query:

select o.name, count(o.officeID) 
from SysTransaction t 
left join SysOffice o on t.officeID = o.officeID 
group by o.officeID
Jerrime25
  • 13
  • 1
  • 8
  • Possible Duplicate. http://stackoverflow.com/questions/7285714/linq-with-groupby-and-count – Chris Feb 26 '16 at 01:52
  • no its not. it didn't use left join and I already tried it. – Jerrime25 Feb 26 '16 at 02:00
  • https://msdn.microsoft.com/en-us/vstudio/ee908647.aspx#leftouterjoin or https://www.linqpad.net/ – Marco Bong Feb 26 '16 at 02:24
  • it don't have count. – Jerrime25 Feb 26 '16 at 03:53
  • please help me, this problem damages me for last 2 days. – Jerrime25 Feb 26 '16 at 03:57
  • Possible duplicate. [stackoverflow.com/questions/695506/linq-left-join-group-by-and-count](http://stackoverflow.com/questions/695506/linq-left-join-group-by-and-count) – Colby Feb 26 '16 at 04:02
  • Are you shure that want to group by o.officeID not t.officeID. o.officeID may be null in case of left join, also count(o.name) tells that more probably to group by t.officeID, because it will calculate only non null values from right side of join. – Slava Utesinov Feb 26 '16 at 06:25
  • Please show what you've tried and the problem you've run into, rather than just saying it didn't work. – Jon Skeet Feb 26 '16 at 06:44
  • this is the LINQ: _from trans in db.SysTransactions .Where(x => x.datestart >= '2016-01-04' && x.dateend <= '2016-01-08' && (x.SysOffice.code + " - (" + x.SysOffice.name + ")").Contains("MIS")) join office in db.SysOffices on trans.officeID equals office.officeID into transoff select new { trans.officeID, trans.SysOffice.name, trans.SysOffice.code, count = transoff.Count() };_ this is the result: http://postimg.org/image/upimxt4fp/ – Jerrime25 Feb 26 '16 at 07:13
  • To clarify, you want a count of transactions by office, but not all transactions have an office ? – Murph Feb 26 '16 at 08:53
  • A good question would have included the SQL that supposeldy is working, your attempts at LINQ, a desription how it fails and a statement what you want to achieve. – TaW Feb 26 '16 at 16:34
  • **@Murph:** I want to count how many times an office had a transaction in my list. ex: Office1 (count 2). this image will help you to visualized.postimg.org/image/upimxt4fp – Jerrime25 Feb 27 '16 at 00:56

1 Answers1

0

try this 

from t in sysTransaction
join o in sysOffice on t.OfficeId equals o.OfficeId into joined
from one in joined
group one by one.OfficeId into grouped
select new
{
    officeId = grouped.Key,
    count = grouped.Count()
};
GlitterX
  • 16
  • 2