How to query data from multiple tables in EF?

Question

This the query I have which is used to get data from multiple tables from SQL server

SELECT
    Blob.transactionId,
    Blob.status,
    COUNT(Bsp._id) AS processed,
    Blob.total, 
    Blob.reason,
    (SELECT MAX(MyMaxName) 
     FROM (VALUES 
               (MAX(Fail.ProcessTime)), 
               (MAX(Bsp.ProcessTime)) 
          ) MyAlias(MyMaxName)) AS ProcessTime ,
    COUNT(DISTINCT Fail.id) AS failed
FROM 
    BlobStatus AS Blob
LEFT JOIN 
    BspResponse AS Bsp ON Bsp.tid = Blob.transactionId
LEFT JOIN 
    FailedResponse AS Fail ON Fail.transactionId = Blob.transactionId
WHERE 
    Blob.transactionId = "084f4b75-c38e-449c-a9eb-148dce67111a"
GROUP BY 
    Blob.transactionId, Blob.status, Blob.total, Blob.reason, Blob.failed

In .NET code, I tried in this way by calling the below-mentioned method. By referring to this tutorial https://www.entityframeworktutorial.net/Querying-with-EDM.aspx. Entity SQL

public BlobResponse GetBlobDetailsById(string tid)
{
        try
        {
            string sqlQuery = $"SELECT  " +
                "  Blob.transactionId, Blob.status,   " +
                "COUNT(Bsp._id) AS processed,  Blob.total,   " +
                " Blob.reason, " +
                "(SELECT" +
                  " MAX(MyMaxName) " +
                  "FROM(VALUES " +
                  $"(MAX(Fail.ProcessTime))," +
                  $" (MAX(Bsp.ProcessTime)) ) " +
                  "MyAlias(MyMaxName)" +
                ") as ProcessTime, " +
                "COUNT(DISTINCT Fail.id) AS failed  " +
                "FROM BlobStatus AS Blob                 " +
                "LEFT JOIN BspResponse AS Bsp ON Bsp.tid = Blob.transactionId   " +
                "LEFT JOIN FailedResponse AS Fail ON Fail.transactionId = Blob.transactionId " +
                $"WHERE Blob.transactionId = {tid} " +
                " GROUP BY Blob.transactionId, Blob.status, Blob.total, Blob.reason, Blob.failed";

            var objctx = ((IObjectContextAdapter)_mriDbContext).ObjectContext;
   
            ObjectQuery<BlobResponse> res = objctx.CreateQuery<BlobResponse>(sqlQuery);
            BlobResponse blobResponse = res.First<BlobResponse>();

            return blobResponse;
        }
        catch (Exception e)
        {
            _logger.LogInformation(e.Message);
            return null;
        }
    }

I am getting the following error

[17:27:43 INF] Unable to cast object of type 'Flash.MultiRecordInquiry.Subscriber.Models.Context.MriDbContext' to type 'System.Data.Entity.Infrastructure.IObjectContextAdapter'.

This is the DB context

public class MriDbContext : DbContext
{
    public MriDbContext(DbContextOptions<MriDbContext> options) : base(options)
    { }

    public DbSet<BSPReponse> BspResponse { get; set; }
    public DbSet<BspStatusDetails> BspStatusDetails { get; set; }
    public DbSet<FhaStatusDetails> FhaStatus { get; set; }

    public DbSet<BlobStatus> BlobStatus { get; set; }

    public DbSet<FailedResponse> FailedResponses { get; set; }
}

I am new to EF and not familiar to run such complex queries in Entity Framework. Please help me how to resolve this issue

Thanks in advance

Answer 1

I hope it might help full to others, Like after seeing many suggestions and replies from others. I just figured it out, there is an altered way where you can execute the raw query as in SQL server.

For example, you can execute the raw query by using like this in EF

using (var command = _ctx.Database.GetDbConnection().CreateCommand())
       {
           command.CommandText = "select * from MARK where tid ='123'";
           _ctx.Database.OpenConnection();
           using (var reader = command.ExecuteReader())
                {

                    reader.Read();
                    var firstColumnObject = reader.GetValue(0); 
                }
             _ctx.Database.CloseConnection();
       
        }