SQL Server Inner Join

Join's ON method accepts a simple Object equality expression and infers the actual relationship from EF metadata:

var query = DbContext.Set<ProductCategoryPrice>()
                .Query((Products products, Categories category, ProductCategoryPrice alias) =>
                {
                    var result = SELECT<ProductCategoryPrice>(products.ProductName.@as(alias.Product),
                        category.CategoryName.@as(alias.Category),
                        products.ListPrice.@as(alias.ListPrice));
                    FROM(products).JOIN(category).ON(products.Category == category);

                    return result;
                })
                .OrderByDescending(p => p.Product);

foreach (var product in query.Take(3))
    Console.WriteLine((product.Product, product.Category, product.ListPrice));

There is also an option to specify the relationship manually:

var query = DbContext.Set<ProductCategoryPrice>()
                .Query((Products products, Categories category, ProductCategoryPrice alias) =>
                {
                    var result = SELECT<ProductCategoryPrice>(products.ProductName.@as(alias.Product),
                        category.CategoryName.@as(alias.Category),
                        products.ListPrice.@as(alias.ListPrice));
                    FROM(products).JOIN(category).ON(products.Category.CategoryId == category.CategoryId);

                    return result;
                })
                .OrderByDescending(p => p.Product);

foreach (var product in query.Take(3))
    Console.WriteLine((product.Product, product.Category, product.ListPrice));

Any number of entities can be joined:

var query = DbContext.Set<ProductCategoryBrandPrice>()
                .Query((Products products,
                    Categories category,
                    Brands brand,
                    ProductCategoryBrandPrice alias) =>
                {
                    var result = SELECT<ProductCategoryBrandPrice>(products.ProductName.@as(alias.Product),
                        category.CategoryName.@as(alias.Category),
                        brand.BrandName.@as(alias.Brand),
                        products.ListPrice.@as(alias.ListPrice));
                    FROM(products).JOIN(category).ON(products.Category == category).JOIN(brand).ON(products.Brand == brand);

                    return result;
                })
                .OrderByDescending(p => p.Product);

foreach (var product in query.Take(3))
    Console.WriteLine((product.Product, product.Category, product.Brand, product.ListPrice));

< BACK | HOME