Using Entity Framework to return a very specific set of data from MSSQL

Question

I was able to create a .NetCore 2.1 website with Enitity Framework by following a Microsoft tutorial.

The web app connects to a MS SQL Database and uses scaffolding to translate the database tables into classes.

But all it is doing behind the scenes is basic queries like 'select * from myTable' etc.

For example, I have this simple controller that just gets all the Players in the PlayerList table:

        // GET: PlayerLists
    public async Task<IActionResult> Index()
    {
        return View(await _context.PlayerList.ToListAsync());
    }

This one is a little more complicated, but it's really just getting one Player:

        // GET: PlayerLists/Edit/5
    public async Task<IActionResult> Edit(int? id)
    {
        if (id == null)
        {
            return NotFound();
        }

        var playerList = await _context.PlayerList.FindAsync(id);
        if (playerList == null)
        {
            return NotFound();
        }
        return View(playerList);
    }

This works, but I need something more complex to get a very specific set of data from my database.

How can I add a query that runs a very specific query with SQL Joins, case statements, and group by clauses?

Thanks!

Querying is accomplished via LINQ http://www.entityframeworktutorial.net/efcore/querying-in-ef-core.aspx — Adam Vincent, Nov 07 '18 at 15:25

score 2 · Accepted Answer · answered Nov 08 '18 at 05:19

In EF core 2.1 we use Linq to query data.You could see how to use Joins here and group by here. The linq equivalent of transact sql CASE WHEN THEN is the conditional operator ?:

Below is a simple demo which combines them to do the query:

1.Models

public class Product
{
    [Key]
    public int ProductID { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    public int CategoryID { get; set; } 
    public Category Category { get; set; }
}
public class Category
{
    [Key]
    public int CategoryID { get; set; }
    public string Name { get; set; }


    public IList<Product> Products { get; set; }
}

2.Query in controller

var result = from p in _context.Products
                     group p by p.CategoryID into pg
                     // join *after* group
                     join c in _context.Categories on pg.FirstOrDefault().CategoryID equals c.CategoryID
                     select new
                     {
                         CategoryName = c.Name == "oldName" ? "newName" : c.Name,//Replace Case statement
                         Products = pg
                     };

Using Entity Framework to return a very specific set of data from MSSQL

1 Answers1