3

i am trying to join 4 tables and group them accordingly and calculate a value. 

var query = from sd in sdddd
                join ei in eiii.Where(ei1 => ei1.bi == 110) on sd.sdi equals ei.sdi
                join pch in pchhhh.Where(bpch =>  bpch.bi == 110) on  sd.sdi equals pch.sdi 
                join ass in baaaa.Where(pc => pc.bi == 110) on pch.bi equals ass.bi
            where ei.bi == 110
            group new { sd,ei,pch, ass} by new { sd.dis,sd.dn } into x
                select new bs
                {
                    val1 = x.key.dn,
                    val2 = x.key.dis,
                    val3 = x.key.sdi,
                    val4 = x.key.di,
                    val5 = x.Sum(a=>a.pch.ab == "A" ? (a.pch.ans * a.ass.prm) : (a.pch.sb == "B" ? (a.pch.ans * a.ass.pim) : 0))
                };

The issue i found was with group by function. If i remove the groupby it return data very fast, but takes lot of time when using groupby

select  sd.[sdi],sd.[dnum] as 'Dept #',sd.[desc],
format(sum(case when pch.[ab]='A' then pch.[ans]*ass.prm 
else pch.[ans]*.pim end),'N2')
FROM [database].[sdddd] as sd
JOIN [database].[eiii] as ei on ei.[sdi] = sd.[sdi]
join [database].[pchhh] as pch on pch.[sdi]=sd.[sdi]
join [database].[ass] as ass on ass.bi = pch.bi 
WHERE ei.bi=110
GROUP BY sd.[desc],sd.[dnum],sd.[sdi]
ORDER BY sd.[dnum]

Original sql query which works fine. I was just trying to convert this query of joining three different tables and grouping them together and converted into linq

sandeep
  • 31
  • 5
  • Welcome to StackOverflow! Please review the [FAQ](https://stackoverflow.com/help/how-to-ask) to make sure you include all of the information needed to help answer this question for you. – Tim Nov 20 '18 at 19:52
  • @tim I am not sure what additional data is required for this post. Please let me know what kind of data is required. – sandeep Nov 20 '18 at 19:56
  • From your post it is hard to figure out what your actual question is. Also, you could include the actual SQL query that you're trying to duplicate in your Linq query. – Tim Nov 20 '18 at 20:00
  • Something is wrong with your `select` after `group by`. `val1`, `val2`, `val3` and `val4` should be taken from `x`, for the first tow definitely `x.Key.dis`, `x.Key.dn`, don't know for the other two. – Ivan Stoev Nov 20 '18 at 20:13
  • I know the pomelo provider for mysql has not been able to (properly) implemented the groupby functionallity yet and perhaps due to some limitations in the source code of ef core. Enable sensitive data logging and you might notice the query being executed in-memory. That means a lot of unnecessary data is being pulled into memory before the groupby is executed. – Silvermind Nov 20 '18 at 22:09
  • Are you using LINQ to SQL, EF or EF Core? Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you? – NetMage Nov 26 '18 at 20:13

0 Answers0