.NET 9 Core MVC - Database qurstions

Question

I have a very old MS SQL database. When I say old, I really mean that it was created and built upon without any adherence to proper database design discipline. Anyways, this is currently running a very outdated combination of asp and asp.net webform website.

My Goal: I am building a completely new web application using a .NET 9 Core MVC project to replace it. However, I need to continue to use this very old database design.

My Problem: I am having difficulty getting database collections that I need to build these pages. I can easily get ALL the table fields and data from ONE table. But I cannot write code or queries to limit what is returned. from the table.

Here is an example of a simple SQL statement that works just fine in SQL Server Manager, but I cannot write the equivalent code in the .NET 9 MVC framework using LINQ, EF or any other database querying code that .NET uses.

SELECT clients.clientid, first, last, COUNT(*)

FROM clients

INNER JOIN clients_servicecodes ON clients.clientid = clients_servicecodes.clientid

INNER JOIN clients_accesslevels ON clients.clientid = clients_accesslevels.clientid

WHERE clients_accesslevels.accesslevel = '2' AND (clients_servicecodes.dept = '2')

GROUP BY clients.clientid, first, last ORDER BY last

Is this just Microsoft's way of simply saying "I need to reconfigure all database tables to conform to modern .NET" ?

Answer 1

EF is a ORM. Its main goal is to map sql tables to objects, and objects to sql tables. This requires that you map all the tables to sets in the database context and defines the keys. once this is done, you can do linq queries.

assume you defined the dbcontext and sets its:

var rows = db.Clients
    .Join(db.ClientsServiceCodes,
          c => c.ClientId,
          csc => csc.ClientId,
          (c, csc) => new { c, csc}
    ) 
    .Join(db.ClientsAccessLevels,
          j1 => j1.c.ClientId,
          cal => cal.ClientId,
          (j1, cal) => new { j1.c, j1.csc, cal}
    ) 
    .Where(j2 => j2.csc.Dept == "2" && j2.cal.AccessLevel == "2") 
    .GroupBy(j2 => new {j2.c.ClientId, j2.c.First, j2.c.Last})
    .Select(g => new {g.ClientId, g.First, g.Last, Count = g.Count()})
    .OrderBy(r => r.Last)
    .ToList();     

if you like coding in sql, look at dapper, which just maps queries to objects:

https://www.learndapper.com