How to return data from different tables using Sql Query in EF Core without using table valued functions/Linq

c# entity-framework entity-framework-6 entity-framework-core

Question

How can i select data from two tables using SQL query using ef core. Below are two tables

 public class Category
    {
        public int Id { get; set; } // Don't want to return this.
        public string Name { get; set; } // Only want to return this.
    }

public class Product
{
    public int ProductId {get;set;}
    public string Name { get; set; }
    public int CategoryId {get;set;}
}

I want to execute Select P.Name , C.Name from tblCategory C JOIN tblProduct P ON C.Id = P.CategoryId in entity framework core.

One option is to use table valued functions but i dont want to consider it? I dont want to use linq because the sql generated is not efficient. Above is just an example not a real scenario.

1
0
2/6/2020 11:20:55 AM

Popular Answer

If you want to do this using EF Core instead of ADO.NET or Dapper, you can use raw Sql Queries as below:

EF Core 2.1 example:

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();

In EF Core 3.1, FromSql is Obsolete, Hence use FromSqlRaw

var blogs = context.Blogs
    .FromSqlRaw("SELECT * FROM dbo.Blogs")
    .ToList();

Similarly, your query can be executed as

var results = context.ProductCategories.FromSqlRaw(Select P.Name as ProductName, C.Name as CategoryName from tblCategory C JOIN tblProduct P ON C.Id = P.CategoryId)

Note: Also, you need to define ProductCategories in context class.

public DbQuery<ProductCategory> ProductCategories { get; set; }

ProductCategory.cs

public class ProductCategory
{
    public string ProductName { get; set; }
    public string CategoryName { get; set; }
}

Here, ProductCategory is known as query type in EF Core 2.1. In EF Core 3.0 the concept was renamed to keyless entity types. It serves as return type for raw Sql Queries.

You can refer docs.microsoft.com for more details

1
2/6/2020 1:49:08 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow