Is there better way to shorten this Lambda expression's result selector?

Question

I'm new in LINQ and Lambda expression. I'm not sure is there a way to shorten the result on this o.z.cust.cust.cust.bintAccountNo. As you can see below, the more table needed to join the longer the resultselector cust.cust.cust. Is there better way to shorten this Lambda expression's result selector?

POCO

public class Tbl1 
 {
        [Key] 
        public long bintAccountNo{ get; set; }
 }


 public class Tbl2
 {
        [Key] 
        public int id { get; set; }
        public long bintAccountNo{ get; set; }
 }


 public class Tbl3
 {
        [Key] 
        public int id { get; set; }
        public long bintAccountNo{ get; set; }
 }

 public class Tbl4
 {
        [Key] 
        public int id { get; set; }
        public long bintAccountNo{ get; set; }
        public int intPartner{ get; set; }
 }

 public class Tbl5
 {
        [Key] 
        public int id { get; set; }
        public int intPartner{ get; set; }
 }

var b = (Tbl1 
.GroupJoin(Tbl2, cust => cust.bintAccountNo, cust2 => cust2.bintAccountNo, (cust, cust2) => new  { cust = cust, cust2 = cust2 })
.SelectMany(cust => cust.cust2.DefaultIfEmpty(), (cust, cust2) => new { cust = cust.cust, cust2 = cust2 })
.GroupJoin(Tbl3, c => c.cust.bintAccountNo, cust3 => cust3.bintAccountNo, (cust, cust3) => new { cust = cust, cust3 = cust3 })
.SelectMany(c => c.cust3.DefaultIfEmpty(), (cust, cust3) => new { cust = cust.cust, cust3 })
.Join(Tbl4, c => c.cust.cust.bintAccountNo, bp => bp.bintAccountNo, (cust, bp) => new { cust = cust, bp = bp })
.Join(Tbl5, z => z.bp.intPartner, part => part.intPartner, (z, part) => new { z = z, part = part }))
.Select(o => new 
  {
      bintAccountNo = o.z.cust.cust.cust.bintAccountNo
  }

Answer 1

Naming

Your entities / POCO classes have badly named members (assuming the entity names themselves aren't really named the way you've posted them), that break the C# naming conventions for public members - they should be PascalCase, ...and what's with the Hungarian notation? Also disemvoweling is never a great idea - call a table Table instead of Tbl, and we'll know you didn't mean Team-Based-Learning... whatever.

public class Table2
{
       [Key] 
       public int Id { get; set; }
       public long AccountNumber{ get; set; }
}

Entities are NOT database tables; they MAP to database tables, and they're classes - so they should follow the naming conventions for C# classes. If they're mapped to tables/columns on SQL Server with different names, you should use the appropriate [Column("columnName")] attributes (or fluent API configurations) to specify the mapping.

Convention over Configuration

Entity Framework is able to infer a lot of things from the way you name things. Having a property called Id in an entity type, is automagically inferred to be that type's [Key] - hence, the explicit attribute is redundant / noise, and should be removed.

Tbl1 is breaking this convention. The [Key] field bintAccountNo should be called Id, and the table should be called Account - so when other related entities have a property named AccountId, Entity Framework knows that's a FK property.

Navigation Properties

One of the greatest things about EF is its ability to simplify the querying of complex relationships between entities, using navigation properties.

public class Account
{
    public long Id{ get; set; }
}

public class Table2
{
    public int Id { get; set; }
    public long AccountId { get; set; }

    public virtual Account Account { get; set; }
}

You mention in a comment that you find the generated T-SQL inefficient when you're using navigation properties; how exactly is it "inefficient"? It's verbose, yes, but that doesn't make it inefficient. Have you performance-profiled it? What's the bottleneck? Could you be missing indexes?

---

Your Query

I like that you're using the method syntax, but it's not clear what's being queries exactly, because the code you posted looks like it's querying the type itself:

var b = (Tbl1.GroupJoin(...));

One would expect something like this:

using (var context = new MyDbContext())
{
    var result = context.Tbl1s.GroupJoin(...));
}

GroupJoin

This is where the query syntax gets handy, see what exactly is a group join? on Stack Overflow:

Join syntax:

from p in Parent
join c in Child on p.Id equals c.Id
select new { p.Value, c.ChildValue }

GroupJoin syntax:

from p in Parent
join c in Child on p.Id equals c.Id into g
select new { Parent = p, Children = g }

And that calls GroupJoin() for you under the hood, and it's easier to mentally picture what your data looks like, I find.

I'm not quite clear on GroupJoin followed by SelectMany - why not just do a regular Join then?