Replace view SQL Server with public List<> in c#.net

Question

I am building a c#.net web application which draws data from a SQL Server. I have a view in SQL Server based on a single table containing multiple records for the employees. At most one record can be active, but it is not necessarily the one with MAX(ID). It contains the history as well as the current status of the employees. This is some legacy I have to work with. In order to get the correct record I group by the employee code, and total the boolean field InDienst ('currently employed'), which can be 1 at most. Based on this selection I can go back and select the correct record applying MAX(ID).

SELECT 
  Personeelscode_ref AS Personeelscode, 
  SUM(InDienst) AS InDienst
FROM dbo.tPersoneel
GROUP BY Personeelscode_ref

This works fine. However, as I am not the only one with access to the database, I would like to move this to the c#.net controller (or a model) and apply a List<> statement in c# which replaces the view. Something like this:

public static List<tPersoneel> listEmployeeAll = EmployeeDB.tPersoneel
            .GroupBy(x => x.Personeelscode_ref)
            .Select( new tPersoneel
            {
                Personeelscode_ref as Personeelscode,
                (InDienst).sum()
            })
            .ToList();

Can you show me what the correct c#.net code would be? Thanks much in advance!

question from:https://stackoverflow.com/questions/66047041/replace-view-sql-server-with-public-list-in-c-net

Answer 1

use this code

List<tPersoneel> listEmployeeAll = EmployeeDB.tPersoneel.GroupBy(x => x.Personeelscode_ref)
    .Select(y => new { 
           Personeelscode = y.Key.Personeelscode_ref, 
           InDienst = y.Sum(x => x.InDienst)
    }).ToList();

or

List<tPersoneel> listEmployeeAll = (from c in EmployeeDB.tPersoneel
      group c by c.Personeelscode_ref into g
      select new
      {
         Personeelscode = y.Key.Personeelscode_ref, 
         InDienst = y.Sum(x => x.InDienst)
      }).ToList();