Stupid Entity Framework tricks

StupidEntityFrameworkTricks.cs

// ----------------------------  Left outer join  --------------------------------------------------------------------

            var ecpNodes =
                (from cp in _db.CustomerProduct
                 join ec in _db.EnvironmentCustomer on cp.CustomerId equals ec.CustomerId
                 join ecp in _db.EnvironmentCustomerProduct
                     on new { ec.EnvironmentId, cp.CustomerId, cp.ProductId }
                     equals new { ecp.EnvironmentId, ecp.CustomerId, ecp.ProductId }
                     into gj
                 // This idiom (group join, then 2nd "from" .DefaultIfEmpty()) results in a left outer join on ECP:
                 from ecp2 in gj.DefaultIfEmpty()
                 where (ec.EnvironmentId == anEnvironmentId)
                       && (cp.IsActive ?? false)
                       && (ec.IsActive ?? false)
                 // Gonna pass this result to another method, so can't use anonymous type
                 select new ECPNodeTuple( ec.Environment, cp.Customer, cp.Product, ec.SisenseNode, ecp2.SisenseNode )
                ).ToArray();

// ----------------------------  All KINDS of shenanigans: inner select, cross product, semi-filtering... ---------------------------

            var ecpKeys = productsToPublish.SelectMany(p => p.SelectedEnvironmentCustomerProductKeys);
            var ecpMap = _db.EnvironmentCustomerProduct
                            .Include( ecp => ecp.Customer  )
                            .Include( ecp => ecp.Product  )
                          .Where(ecp => ecpKeys.Contains(ecp.EnvironmentCustomerProductId))
                          .ToArray()    // Without this, LINQ-to-SQL tries to turn the following into SQL (and fails).
                          .Aggregate(
                              new Dictionary<int, EnvironmentCustomerProduct>()
                              , (map, ecp) =>
                              {
                                  map.Add(ecp.EnvironmentCustomerProductId, ecp);
                                  return map;
                              }
                          );
            var environmentIds = interveningEnvironments.Select( e => e.EnvironmentId ).Distinct();
            var customerIds    = ecpMap.Values.Select( cp => cp.CustomerId ).Distinct();
            var productIds     = ecpMap.Values.Select( cp => cp.ProductId ).Distinct();

            // Flatten the passed-in structure to make joins easier/clearer.
            var flatEcpVersion =
                from prod in productsToPublish
                from ecpKey in prod.SelectedEnvironmentCustomerProductKeys // inner select, same as .SelectMany()
                select new {EcpKey = ecpKey, ProductVersion = prod.ProductVersion};
            // Required versions in intervening environments.
            var custProdVersionCrossEnv =
                (from cp in ecpMap.Values
                 join ecpVersion in flatEcpVersion on cp.EnvironmentCustomerProductId equals ecpVersion.EcpKey
                 from env in interveningEnvironments // Cross product!
                 select new {env.EnvironmentId, cp.CustomerId, cp.ProductId, ecpVersion.ProductVersion})
                .ToArray();
            // Compiler says this isn't used; probably from the earlier composite-key scheme (below).
            var interveningEcpKeys =                        
                (from ecp in custProdVersionCrossEnv
                select new {ecp.EnvironmentId, ecp.CustomerId, ecp.ProductId});
            // All intervening rows, regardless of whether they meet the version criterion, because LINQ can't generate SQL with
            // a join with an internal/local list.
            var interveningEcpRows =
                (from ecp in _db.EnvironmentCustomerProduct
                 // Can't do a composite key CONTAINS for lots of good fundamental SQL reasons (mostly having to do with
                 // indexes) but we can still do better than sucking in the entire table.
                 where environmentIds.Contains( ecp.EnvironmentId  )
                    && customerIds.Contains( ecp.CustomerId )
                    && productIds.Contains( ecp.ProductId )
                 select ecp).ToArray();
            var interveningOldVersions =
                (from ecp in interveningEcpRows
                 join xecp in custProdVersionCrossEnv on new { ecp.EnvironmentId, ecp.CustomerId, ecp.ProductId }
                     equals new { xecp.EnvironmentId, xecp.CustomerId, xecp.ProductId }
                 where ecp.ProductVersion != xecp.ProductVersion
                 select ecp)
                .ToArray();