0

I have 4 tables (Group, Student, Mark, StudentFile, and want to write my SQL query using LINQ in C#.

Here is my query

SELECT
    S.Id,
    S.FirstName,
    S.LastName,
    S.MiddleName,
    SUM(M.CountOfPasses) CountOfPasses,
    (SELECT SUM(CountOfPassesWithARespectful) FROM StudentFile WHERE StudentId = S.Id) WithARespectful
FROM Student S
     LEFT JOIN Mark M ON S.Id = M.StudentId
GROUP BY S.Id, S.FirstName, S.LastName, S.MiddleName

I've already tried something like this:

var  students = (from G in context.Group
                        let v = G.Id
                        from S in context.Student.Where(x => v == x.GroupId)
                        from F in context.StudentFile.Where(x => x.StudentId == S.Id).DefaultIfEmpty()
                        from M in context.Mark.Where(x => x.StudentId == S.Id).DefaultIfEmpty()                              
                        group new
                        {                                
                            F.CountOfPassesWithArespectful,
                            M.CountOfPasses,
                            S.Id
                        }
                        by new
                        {
                            S.Id,
                            S.FirstName,
                            S.LastName,
                            S.MiddleName,
                            S.StartCourse,
                            G.Name,
                            S.Alphagroup                               
                        } into GSF
                        select new DTOStudent
                        {
                            Id = GSF.Key.Id,
                            FirstName = GSF.Key.FirstName,
                            LastName = GSF.Key.LastName,
                            MiddleName = GSF.Key.MiddleName,
                            CountOfPasses = (int)GSF.Sum(p=>p.CountOfPasses),
                            WithRespectful = (int)GSF.Sum(x => x.CountOfPassesWithArespectful),
                            WithOutRespectful = (int)GSF.Sum(x => x.CountOfPasses) - (int)GSF.Sum(x => x.CountOfPassesWithArespectful),
                            Course = ClassMethods.GetAgeFromDates((DateTime)GSF.Key.StartCourse).ToString() +
                                                 ClassMethods.GetShortGroupName(GSF.Key.Name) + GSF.Key.Alphagroup.ToUpper()
                        }).Distinct().ToList();
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Арабов Муллошараф
  • 1
  • 4
    Hi and welcome to StackOverflow. We would need you to give us a problem description. Did you get any errors? if yes what is the error message? – Mong Zhu Apr 06 '21 at 06:21
  • Add the GroupBy after the closing parenthsis and remove the Distinct. Distinct will not work becuase it does not know how to filter a complex object with properties like Id, FirstName, LastName, MiddleName, CountOfPasses, ....... – jdweng Apr 06 '21 at 06:34
  • Thanks. The query works, but does not give the correct result, the values calculated by the aggregate functions are incorrect. If there is only one record related to Student in the Mark or StudentFie table, the correct result will be obtained. but not otherwise – Арабов Муллошараф Apr 06 '21 at 09:56
  • Grouping key is different than in SQL. And why Distinct? – Svyatoslav Danyliv Apr 06 '21 at 10:16
  • Distinct remove this is not a problem, the whole problem is related to the result, the sum functions – Арабов Муллошараф Apr 06 '21 at 10:18
  • @АрабовМуллошараф the whole problem is the incorrect use of EF Core and LINQ. These aren't replacements for SQL. EF Core deals with entities, not tables. A DbContext is a Unit-of-Work, not a database connection or model. It's *EF Core's* job to generate JOINs from the relations between entities – Panagiotis Kanavos Apr 06 '21 at 10:31
  • @АрабовМуллошараф finally, an ORM like EF Core loads object graphs. It's not well suited to reporting queries. At some point, it's easier, faster and more maintainable to write the reporting query in SQL. Look at the original query and the LINQ query. Which is easier to write and optimize? Which is going to run faster? Which is **less** fragile? You can create a view or stored procedure with the reporting query you want and map EF Core entities to it – Panagiotis Kanavos Apr 06 '21 at 10:34
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Apr 06 '21 at 21:07
  • @PanagiotisKanavos I disagree, otherwise there would be no reason for EF/EF Core to support `select`. There is more to EF than loading objects. – NetMage Apr 06 '21 at 21:08
  • @NetMage disagree with what? That `Select` should be used instead of looking for `ExcludeProp`? Or that there's always a mismatch between objects and SQL that no ORM can cover? Or that LINQ is meant to *remove* the need for JOINs, because that's the ORM's job? What's the point of using LINQ if you end up with *more* and *worse* code than a simple SQL query? We had Embedded SQL for 4 decades. Anders Hejlsberg wasn't trying to do that. If he did, LINQ would look very different. – Panagiotis Kanavos Apr 07 '21 at 06:52
  • @PanagiotisKanavos 1.I disagree with "It's not well suited to reporting queries" 2. Anders did LINQ and his group did LINQ to SQL, the ADO.NET group did EF 3. The whole point of LINQ is type safe data queries - it was intended to a better replacement for embedded SQL (e.g. it is impossible to have a SQL injection error in LINQ). LINQ is not an ORM, just a way to query an ORM. – NetMage Apr 08 '21 at 16:22

0 Answers0