You are trying to run subqueries inside a larger query. Even in SQL that is a bad idea. Performance will be awful. I believe the correct approach here is to use grouping since you only seem to care about 2 fields in your dataset. Maybe I'm missing something here but it seems like your query is going to return back a single object for each row in your AllBN_Dam collection but every row will have the same value and it appears that the results would be for the single row so I'd expect most counts to be 1. Additionally you're using bitwise and (&) and I assume you meant logical and (&&).

I'm wondering here if you actually just want 1 result that is a summary for the specific system ID(?) I'm breaking the query up so it is easier to understand.

var group = from p in db.AllBN_Dam
            where p.SystemId == 1 && p.ToDate == yesterday()
            group p by new { TransactionType = p.TransactionTypeId, Kind = p.ElhKind_HvKind } into g
            select new { TransactionType = g.Key.TransactionType, Kind = g.Key.Kind, ItemCount = g.Count(), TotalAmount = g.Sum(x => x.Amount) };
//To simplify the expression, create temp variables to filter data
var bnItems = group.Where(x => x.TransactionType == 1 && x.Kind == 0);
var exteraItems = group.Where(x => x.TransactionType == 1 && x.Kind == 212);
var reverseItems = group.Where(x => x.TransactionType == 1 && x.Kind == 213);
var damageItems = group.Where(x => x.TransactionType == 2);
//One record for all rows
var result = new SmsTypeV2() {
    BNCnt = bnItems.Max(x => x.ItemCount),
    BNAmount = bnItems.Sum(x => x.TotalAmount),
    ExteraCnt = exteraItems.Max(x => x.ItemCount),
    ExteraAmount = exteraItems.Sum(x => x.TotalAmount),
    ReverseCnt = reverseItems.Max(x => x.ItemCount),
    ReverseAmount = reverseItems.Sum(x => x.TotalAmount),
    DamageAmount = damageItems.Sum(x => x.TotalAmount),
};

Also write the failed modified case2 in case somebody need it. Seems can't find any active issue for the 3 exceptions.

failed modified case2

1. System.InvalidOperationException:“variable 'outer' of type 'ConsoleApp15.Test002' referenced from scope '', but it is not defined”

   (not sure if this will happen with normal join or cross apply) Looks like 2.1 regression: "variable referenced from scope '', but it is not defined" exception in GroupBy query translation

   11904, but it's already fixed.

            /*var e = ctx.Test002.Where(outer => ctx.Test002
                            .Where(inner => inner.GroupByKey == outer.GroupByKey)
                            .OrderBy(inner => inner.OrderByKey)
                            .Select(inner => new { outer.PK1, outer.PK2 })//This doesn't work, should be inner here, just hit this exception by mistake
                            .FirstOrDefault() == new { outer.PK1, outer.PK2 })
                            .ToList();*/

1. System.InvalidOperationException:“No coercion operator is defined between types 'System.Int32' and 'System.Nullable`1[System.Boolean]'.”

            ctx.Test001.Where(d1 => ctx.Test001
                .Select(d2 => new { A = d2.PK })
                .OrderBy(d2 => d2.A)
                .Take(2)
                .Where(d2 => d2.A == 1)
                .Select(d2 => true)
                .FirstOrDefault())
                .ToList();

1. Wrong SQL:

//simple .Select(inner => true).FirstOrDefault() works well.

            var f = ctx.Test002.Where(outer => ctx.Test002
                            .Where(inner => inner.GroupByKey == outer.GroupByKey)
                            .OrderBy(inner => inner.OrderByKey)
                            .Select(inner => new { inner.PK1, inner.PK2 })
                            .Take(2)
                            .Where(inner => inner.PK1 == outer.PK1 && inner.PK2 == outer.PK2)
                            //.Select(inner => 1)
                            .Select(inner => true)
                            .FirstOrDefault()
                            )
                            .ToList();

->

  SELECT [outer].[PK1], [outer].[PK2], [outer].[GroupByKey], [outer].[OrderByKey]
  FROM [Test002] AS [outer]
  WHERE COALESCE((
      SELECT TOP(1) [t].[PK1], [t].[PK2]
      FROM (
          SELECT TOP(2) [inner].[PK1], [inner].[PK2]
          FROM [Test002] AS [inner]
          WHERE [inner].[GroupByKey] = [outer].[GroupByKey]
          ORDER BY [inner].[OrderByKey]
      ) AS [t]
      WHERE ([t].[PK1] = [outer].[PK1]) AND ([t].[PK2] = [outer].[PK2])
  ), 0) = 1

ClientEvaluation:

//NOTE: It's really strange that simply add an .Select(inner => 1) before .Any() make it works

            var c = ctx.Test002.Where(outer => ctx.Test002
                            .Where(inner => inner.GroupByKey == outer.GroupByKey)
                            .OrderBy(inner => inner.OrderByKey)
                            .Select(inner => new { inner.PK1, inner.PK2 })
                            .Take(2)
                            .Where(inner => inner.PK1 == outer.PK1 && inner.PK2 == outer.PK2)
                            //.Select(inner => 1)
                            .Any()
                            )
                            .ToList();

//4. An simpler case of c

           var c1 =  ctx.Test001.Where(d1 => ctx.Test001
                .Select(d2 => new { PK = d2.PK })
                .OrderBy(d2 => d2.PK)
                .Take(2)
                .Where(d2 => d2.PK == 1)
                //.Select(d2 => 1)
                .Any())
                .ToList();

//It's ok that it just not works, it may get the same result with case2(Take(2)->Take(1))

            var d = ctx.Test002.Where(outer => ctx.Test002
                            .Where(inner => inner.GroupByKey == outer.GroupByKey)
                            .OrderBy(inner => inner.OrderByKey)
                            .Select(inner => new { inner.PK1, inner.PK2 })
                            .FirstOrDefault() == new { outer.PK1, outer.PK2 })
                            .ToList();

dbcontext

public partial class TestContext : DbContext
{
    public TestContext()
    {
    }
    public TestContext(DbContextOptions options)
        : base(options)
    {
    }
    public virtual DbSet Test001 { get; set; }
    public virtual DbSet Test002 { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            //optionsBuilder.UseNpgsql("Host=127.0.0.1;Database=test001;Username=postgres;Password=123456");
            optionsBuilder.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=Test_Database001;Trusted_Connection=True;");
            //optionsBuilder.ConfigureWarnings(warnings => warnings.Throw(Microsoft.EntityFrameworkCore.Diagnostics.RelationalEventId.QueryClientEvaluationWarning));
        }
        var logger = LoggerFactory.Create(conf =>
        {
            conf.AddConsole();
        });
        optionsBuilder.UseLoggerFactory(logger);
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity(entity =>
        {
            entity.HasKey(d => d.PK);
        });
        modelBuilder.Entity(entity =>
        {
            entity.HasKey(d => new { d.PK1, d.PK2 });
        });
    }
}
public partial class Test002
{
    public int PK1 { get; set; }
    public int PK2 { get; set; }
    public int GroupByKey { get; set; }
    public int OrderByKey { get; set; }
}
public partial class Test001
{
    public int PK { get; set; }
    public int GroupByKey { get; set; }
    public int OrderByKey { get; set; }
}

How do I select top 5 records in LINQ?

LINQ Query To Select Top 5 Records..

var list = (from t in ctn.Items..

where t.DeliverySelection == true && t.Delivery.SentForDelivery == null..

orderby t.Delivery.SubmissionDate..

select t). Take(5);.

What method would you use in a LINQ query to sort results?

LINQ includes five sorting operators: OrderBy, OrderByDescending, ThenBy, ThenByDescending and Reverse.

What is the sum of values in a list in LINQ?

Linq. The sum() function calculates the sum of items which present in the list or collection, and it returns the sum of numeric values as a result. It works with int, decimal, double, float, nullable, non-nullable values.

How do you sort using LINQ?

The LINQ Sorting operators are of five different types they are as follows:.

OrderBy. OrderBy sorting is used to sort the values in the sequence based on a particular field in ascending order or descending order; by default, it sorts elements in ascending order. ... .

OrderByDescending. ... .

ThenBy, ThenByDescending. ... .

Reverse..