how to execute complex sql query through fromrawsql in asp.net 6

We can fetch data through ‘FromSqlRaw’ from a single table but how to
do it if we are fetching data from multiple tables and using joins,
union

Well, based on your scenario and description, it seems that you would like to execute complex query which involved lot of SQL JOIN, Union even other SQL functionalities, actually, there might numerous way to implement that, but using SQL views upon your Entity context would be more easier and convenient to execute join and other query, no mater how complex it is.

In order to do that, you can create a SQL view, the query you would like to write including all of your requirements, then this view would return your expected entity field or property your can say, now create a class based on the field or property you have returned within your view. Finally, introduce that class as DbSet<YourClass> in your dbContext,

Now you can, call it from your controller using FromSqlRaw and you can set any conditional on your View.

Let’s have a look in practice how we can implement in code snippet,

SQL View:

CREATE VIEW RunViewInAspNetCore
AS
SELECT Id, Title, Body, Author FROM Post

This should looks like as following:

Demo Class:

public class ComplexTSQLUsingView
    {
        [Key]
        public int Id { get; set; }
        public string Title { get; set; }
        public string Body { get; set; }
        public string Author { get; set; }
    }

DbContext:

public class ApplicationDbContext : DbContext
    {

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

        }
     
       
        public DbSet<ComplexTSQLUsingView> ComplexTSQLUsingViews { get; set; }
      

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            
        }
    }

Controller:

public class ComplexTSQLUsingViewController : Controller
    {
        private readonly ILogger<HomeController> _logger;

        private readonly ApplicationDbContext _context;

        public ComplexTSQLUsingViewController(ILogger<HomeController> logger, ApplicationDbContext context)
        {
            _logger = logger;
            _context = context;
        }

        public async Task<IActionResult> Index()
        {
            var author = "Kiron";
            var sqlQuery = String.Format("SELECT * FROM [RunViewInAspNetCore] WHERE Author ='{0}'", author);
            var sqlresult = _context.ComplexTSQLUsingViews.FromSqlRaw(sqlQuery).ToList();
            return Ok(sqlresult);
        }

    }

Note: Set additional condition here in sqlQuery variable in order to keep clean your view. In addition, property name should be unique and duplicate property name should used Alias and use alias name in C# property.

Output: