LINQ- Compiled queries and data layers

[Justin Dutoit]

Hey. I've been doing some reading and my first conclusion is that it seems
to me that LINQ to entities will be around for awhile.
Regarding performance though, before LINQ we all used stored procedures
because they are compiled and the fastest way to get data. I'd appreciate
some opinions on what the equivalent is now, i.e. best practice data layer
code using LINQ.
We also used to have wrappers for stored procs in the data layer (now LINQ
queries), and we unpacked the results into List<MyObject> in the business
layer. Opinions on the little business layer and data layer methods below
pls? Thanks for your time.

// In business layer file
public List<Product> GetSecondaryProducts(string categorySecondary)
{
List<Product> productList = new List<Product>();
Product thisProduct;

foreach (Products theProduct in
DefineSecondaryProducts(categorySecondary))
{
thisProduct = new Product();
thisProduct.Brand = theProduct.Brand;
thisProduct.ProductName = theProduct.Productname;
thisProduct.Price = theProduct.Price;

productList.Add(thisProduct);
}

return productList;
}

// In 'data' layer file
public IQueryable<Products> DefineSecondaryProducts(string
categorySecondary)
{

Quickshop35sp1 QS35sp1Entities = new Quickshop35sp1();
ObjectQuery<Products> products = QS35sp1Entities.ProductsSet;

IQueryable<Products> productsQuery =
from p in products
where p.CategorySecondary ==
categorySecondary
select p;

return productsQuery;
}

 

[Pavel Minaev]

Hey. I've been doing some reading and my first conclusion is that it seems
to me that LINQ to entities will be around for awhile.
Regarding performance though, before LINQ we all used stored procedures
because they are compiled and the fastest way to get data.

Stored procedures haven't had any performance advantages over
dynamically generated SQL for a while now - pretty much all major
databases equally well cache compiled versions of all SQL that's
executed, so the difference there is nil in practice. Stored
procedures still have the advantage of potentially better security (if
used correctly); whether it is worth it or not really depends on what
you're actually doing..

We also used to have wrappers for stored procs in the data layer (now LINQ
queries), and we unpacked the results into List<MyObject> in the business
layer. Opinions on the little business layer and data layer methods below
pls? Thanks for your time.

    // In business layer file
    public List<Product> GetSecondaryProducts(string categorySecondary)
    {
            List<Product> productList = new List<Product>();
            Product thisProduct;

            foreach (Products theProduct in
DefineSecondaryProducts(categorySecondary))
            {
                thisProduct = new Product();
                thisProduct.Brand = theProduct.Brand;
                thisProduct.ProductName = theProduct.Productname;
                thisProduct.Price = theProduct.Price;

                productList.Add(thisProduct);
            }

            return productList;
    }

    // In 'data' layer file
    public IQueryable<Products> DefineSecondaryProducts(string
categorySecondary)
    {

        Quickshop35sp1 QS35sp1Entities = new Quickshop35sp1();
        ObjectQuery<Products> products = QS35sp1Entities.ProductsSet;

        IQueryable<Products> productsQuery =
                                            from p in products
                                            where p.CategorySecondary ==
categorySecondary
                                            select p;

        return productsQuery;
    }

Some people recommend doing so in LINQ, but I really don't see a
point. For SQL statements, it was clear - wrap all the ADO.NET mess
into a clean call. The disadvantage there - that you had to write a
new method for every query needed anywhere in your app, effectively
introducing tight coupling - was known, but the advantage was strong
enough. With LINQ, it does all the wrapping for you - it already
abstracts away "database-specificness" of your queries (especially if
you use IQueryable actively) - you can swap the DB implementation with
in-memory collections, for example, and the code wouldn't notice
(ideally - there would probably be minor differences still, but in
practice that's unavoidable with any approach). Having wrapper methods
can also give the illusion that you're abstracting away the schema,
but it really still is there in names of helper methods and their
parameters...

 

[Arne Vajhøj]

Stored procedures haven't had any performance advantages over
dynamically generated SQL for a while now - pretty much all major
databases equally well cache compiled versions of all SQL that's
executed, so the difference there is nil in practice.

There are no performance gain due to execution plan.

There can be a big performance gain by reducing the number
of app-DB interactions.

That did not seem to be the case here, but still worth noting.

Arne