LINQ or SQL

[TysonLPrice]

I'm on a team that is going to have input into a new small (that's relative but maybe 15 forms, ten SQL tables, and plenty of database activity) C# .Net windows form application and we are knocking around some standards to go by. Someone on the team has suggested using LINQ versus SQL as the standard query language. That will be coupled with a MVP presentation model to MSSQL server. I'm looking at the various write ups using Google and see the advantages and disadvantages. My career focus has mostly been supporting canned or already written applications so I cannot honestly speak in depth about the advantages or disadvantages.

From a personal standpoint I don't want to add another layer between me and the database and I prefer more control so that is good enough for me to choose SQL. Plus syntactically I just don't care for LINQ. Those arguments will not win the day however.

Some of the members here are very well qualified to discuss things like this and I am curious about the general consensus about picking one over the other (saying you have to). And of course I expect the usual answer "it depends"

So as I continue researching I'm hoping to take points from any discussion that might be generated from here and research them further.

Here is a small pro and con list I came across from here: https://social.msdn.microsoft.com/Fo...orum=linqtosql

Advantages
•Quick turn around for development
•Queries can be dynamically
•Tables are automatically created into class
•Columns are automatically created into properties
•Relationship are automatically appeaded to classes
•Lambda expressions are awesome
•Data is easy to setup and use

Disadvantages
•No clear outline for Tiers
•No good way of view permissions
•Small data sets will take longer to build the query than execute
•There is an overhead for creating queries
•When queries are moved from sql to application side, joins are very slow
•DBML concurrency issues
•Hard to understand advance queries using Expressions

[FunkyDexter]

it depends

Personally I quite like Linq To SQL. I find it easier to read in the Visual Studio IDE than "normal" sql strings and it's consistent with any other LINQ stuff you've used in the code (which I use heavily)

Two major disadvantages you should consider though:-
1. You will be tied to SQL Server. If you want to port to a different platform later you will have to either rewrite your DB access code (which you will, of course, have abstracted into it's own layer ) or you will need to write your own "Linq to whatever" component (this is MS's recommended approach and they've provided an interface you need to implement but I don't know of anyone actually doing it and I suspect it's not trivial). Of course, you'd have to rewrite you DAL whatever you did but the changes required to "normal" sql strings are likely to be fairly small while moving away from a Linq To SQL approach would be pretty major.
2. This will move you away from having all your data access in sprocs. I'm personally not a huge fan of sprocs for a number of reasons but they certainly do come with a lot of pros as well as cons. TechGnome and SzLamany in particular have built whole architectures based on sprocs so I'll leave it to them to fill in what you'd be missing out on in this regard.

As I said, I like Linq to SQL and it would be my recommendation if you know you won't be switching DB platform later but it's by no means cut and dried... it depends.

edit>BTW, some of your disadvantages look incorrect to me:-

• No clear outline for Tiers - you should still implement a separate DAL whether you're using Linq to SQL or not so there is a clear separation. You certainly shouldn't allow the choice of Linq To SQL to tempt you into allowing the Data Access to creep up into the BLL.
• When queries are moved from sql to application side, joins are very slow - I don't think (though I'm not 100% sure) that that's true. The query is still built up and executed as a single logical operation, exactly as if you'd built up the sql string yourself. It's really just a different syntax for doing so.
• DBML concurrency issues - Not sure what you mean by that. Again, I don't think there would be any difference between using Linq or building your own sql strings in this regard.

[techgnome]

Disclaimer - I started this, then left the window open while I did something else for a bit... so..... eh..


While I'm a fan of LINQ in general, I prefer the functional LINQ and have never used ti for queries. I use it to iterate or manipulate the data once I have it.

Here are my responses to the list:
Advantages
•Quick turn around for development
Actually, I can build a query and code for it faster than I can figure out how LINQ-to-SQL works
•Queries can be dynamically
That's a questionable advantage
•Tables are automatically created into class
Great! Now normalize the data and see how wonderful that really is
•Columns are automatically created into properties
Awesome, as long as your data truly reflects the real world. I've got cases where this isn't the case.
•Relationship are automatically appeaded to classes
Only if it's known - ala FKey ... again, it's not always there.
•Lambda expressions are awesome
No, they are powerful but they can also get in the way. Using them for the sake of using ghtem is the wrong reason to use them.
•Data is easy to setup and use
Sometimes that in of itself is an issue. as long as the data is simple, that's fine. get into complex data and relations and it becomes a nightmare.

Disadvantages
•No clear outline for Tiers
•No good way of view permissions
•Small data sets will take longer to build the query than execute
•There is an overhead for creating queries
•When queries are moved from sql to application side, joins are very slow
•DBML concurrency issues
•Hard to understand advance queries using Expressions

I agree with all of the above... to add:
1) not only is there no good way to view permissions, being able to apply permissions becomes harder.
2) debugging them can be a real PITA!

-tg

[Shaggy Hiker]

Wear depends

I wouldn't go with LINQ to SQL unless you have people who already know it. I strongly doubt that the advantages would outweigh the cost of learning it, but if that cost was already paid, then that's fine. Another option you might consider would be the Entity Framework, which I thought was intended to replace LINQ to SQL, but nothing ever seems to be really replaced. EF was pretty rough to start out with, and I rejected it in an early incarnation. I suppose I should look again, because there were many advances in later additions that could really help.

However, one of the points that TG made I feel is critical...and usually unimportant. Most databases have pretty straightforward relationships with reality. For example, if you were coming up with the typical inventory system, you might have items, orders, customers, and so forth, each of which would be a table with records that correspond to pretty obvious real items with obvious relationships between them. There are other problems that don't have such obvious mappings. For example, I'm working on a project where the business objects take the data from the DB and transform it into things that look like reality, but the underlying DB doesn't look at all like what is represented in the business objects. The data isn't even associated with the physical entities that people see.

So, if the DB is a pretty straightforward mapping to reality, then I think that things like LINQ to SQL makes quite a bit more sense. If the DB doesn't have a straightforward mapping to reality...well, those tools probably won't give you much of an advantage at all. The tools always come with some expectations as to how they will be used. As long as the problem you are working on fits well within those expectations, then all is well. Once the problem doesn't fit the expectations so well, then the changes you have to make can render the tool an impediment rather than a benefit.

And, I'll end with a comment about this statement:

Disadvantages
•No clear outline for Tiers

While it may be the case that there is no clear outline for tiers, if you look closer it's easy to tell the tracks of the tiers.

[TysonLPrice]

Well...the tierrible puns have begun signaling the end of this thread but I appreciate the input so far. It has given me more key\buzz words to focus on. My intent is to post the thread to my team members.

Thanks!

[dday9]

What I did in my last project was use SQL to initially get the data then I used LINQ to manipulate it. When I was finished with the data, I used SQL again to update the changes. This was a nice balance and I find manipulating data using LINQ with VS is much simpler. However, there is nothing wrong with strictly using SQL many people have for quite some time now. My personal opinion is that time are a changing and you should implement LINQ in your projects.

[dilettante]

There's LINQ and LINQ: several different ones.

LINQ to SQL is the one with a clouded future. While clearly deprecated it is also clearly still on life support being brought forward into new .Net releases and getting bug fixes. Enhancements? Future viability? That's where the doubt comes in.

While it may be a ticking timb bomb, LINQ to SQL has hung in all the way from first deprecation in 2008 or so through today. That suggests so much code out there depends on it now it can never go away.

So if you like fat and sloppy DALs that add indirection and block many DBMS performance features... you could probably do worse and it should remain viable for some time. It beats cobbling the SQL together by hand and walking result sets in your code I suppose. Though all it does is generate the SQL intepretively and then intepretively map the result set into objects - all behind your back. At least the interpreter is supposed to be pretty optimized.

Of course the problem doesn't go away because now your code must cobble together and walk object trees instead.

[NeedSomeAnswers]

I just don't like any extra layers between me and the Database.

Any extra layers you add whether it is Linq2SQL or the the Entity Framework or something else just abstracts away your DAL, and i feel that in particular if you don't understand it well you can lose performance.

I'm personally not a huge fan of sprocs

Also personally i am a fan of sprocs and so that would be another problem for me. Its a very rare occasion where you move your application to a new database engine.

I Love Linq, but i tend to to use it on my lists of objects after i have returned them from by DAL or Web Service which ever is serving them to me.

[szlamany]

I just don't like any extra layers between me and the Database.

Also personally i am a fan of sprocs and so that would be another problem for me. Its a very rare occasion where you move your application to a new database engine.

These two points - yes - YES - exactly.

1) What is the return on investment on adding extra layers of abstraction? You are either going to show a single record or a grid of rows of records - getting that from SQL is KISS-native.

2) If your database gives you a handy place to put queries and logic (SPROCS) - why would you avoid them? What is the ROI in NOT USING these database objects?

[szlamany]

My personal opinion is that time are a changing and you should implement LINQ in your projects.

SYBASE had SQL embedded in COBOL a long, long ago (decades...).

I used it in the Y2K debacle...

Same crap - different day.

https://en.wikipedia.org/wiki/Embedded_SQL

[NeedSomeAnswers]

To be fair dday was talking about using Linq in general in your project NOT Linq2SQL which are different things, Linq itself is great and its use should be encouraged.

Being able to query lists of objects using Linq is one of my favourite things they have introduced to .Net

[FunkyDexter]

What is the ROI in NOT USING these database objects?

For me there are two broad downsides to Sprocs.

1. Lack of portability (not an issue in many (probably most) line of business apps)
2. They're inherently procedural so you lose out on a lot of OO goodness. That means they don't decompose business logic as well as you can in a middle tier, they're difficult to unit test, difficult to automate testing etc.

As an optional third, I think it's probably easier to find .Net developers than TSQL developers but that's anecdotal

I don't mean to dismiss sprocs. They offer an awful lot of benefits and I was particularly intrigued by the architectures you and TG were describing in a recent thread but, for my preference, I think a "programmed" (for want of a better word) middle tier is a more natural home for business logic.

I can't help feeling my opinion is coloured by having worked on poorly engineered sproc based system though. In which case the failing was probably down to the poor engineering rather than the concept of using sprocs. I think I've just seen one too many monolithic 10 page sprocs which contain a bunch of code duplicated in other monolithic 10 page sprocs, most of which should have been broken out to separate sprocs/udfs to support code re-use but never was.

[szlamany]

To be fair dday was talking about using Linq in general in your project NOT Linq2SQL which are different things, Linq itself is great and its use should be encouraged.

Being able to query lists of objects using Linq is one of my favourite things they have introduced to .Net

I spend 90% of my time in JavaScript coding now - LINQ is all talk for me

[SJWhiteley]

Personally I find LINQ to be too terse for my liking, and often difficult to debug (I understand that 2015CE has better LINQ debugging capability?) I'm not sure there's a big advantage of LINQ over a simple loop/search, but I guess a lot of people put a lot of value in terseness, over explicit-ness. Again, personal opinion (just like being able to 'do' C#, preference is for VB).

I have also been burned by poor Stored Procedures, containing business rules in a totally obscured, excessively long, stored procedures. Business rules should be - on the whole - transparent. This throws there responsibility for those rules in the hands of a DB admin who, in my experience, are some of the most disconnected and protectionist people ever.

[techgnome]

It's definitely easy to write bad sprocs that are bloated and convoluted... but that's true of any language and is really indicative of the developer and architecture, not the language.

One reason I like them is the ability to easily update them... it's a simple matter to update the sproc w/o having to redeploy a bunch of code. Another reason I like them is the ability to secure things. I can lock down the table, but then grant EXECUTE on an sproc. In our architecture, the users themselves have no rights to the database objects. All actions are taken on their behalf by a server-side process. And it's that process that has access to the objects in the database. 80% of the transactions we do in our system are typical CRUD operations... There are some that aren't, most of those are what we call business processes... mass data updates which can be done in sets... and yes, some of them can get pretty complicated. I jsut reworked one for a client... the base sproc was dynamically strung together... ungh... so I had to unwind it, re-implement the logic that played out in between the concatenations. Turned out they made it more complex than it really needed to be.

But then I work in a nearly strictly MS-world... with SQL Server, IIS, etc... so we don't need to worry about portability. But even if the SQL was right in the code, it's still not portable... most of it would be... but consider just simply moving between Access and SQL Server... dates and other functions are different. So it's a re-write of a query anyways. Plus if you're aiming for portability, you're using an architecture that separates the data store from the app in the first place and let the DB Factory deal with creatiung the right objects and making the call... it's then up to those objects to take care of the database needs.

-tg

[kareninstructor]

We went from desktop apps using data providers to Entity Framework MVP pattern, there are very few complex lambda expressions which can be daunting to maintain if not written well and for the most part tend to use LINQ in many cases because of this. Many of the classes implement generic base classes which makes for less code overall but can be difficult at first to jump into a project if not familiar with classes implementing interfaces, DTO (Data Transfer Objects) and models then on top of this dependency injection (for us in JavaScript and C#)

Speaking strictly LINQ to SQL, many see data contexts first e.g.

public List<string> Countries()
{
    List<string> countries = new List<string>();

    using (CustomerDataContext dc = new CustomerDataContext())
    {
        countries = dc
            .GetTable<Customer>()
            .Select((customer) => customer.Country)
            .OrderBy((country) => country)
            .Distinct()
            .ToList();
    }

    return countries;
}

Or

List<string> countries = dc.Customers
    .Select((customer) => customer.Country)
    .OrderBy((country) => country)
    .Distinct()
    .ToList();

Entity Framework e.g.

public List<string> Countries()
{
    NorthContext dc = new NorthContext();
    List<string> countries = new List<string>();

    countries = dc.Customers
        .Select((customer) => customer.Country)
        .OrderBy((Country) => Country)
        .Distinct()
        .ToList();

    return countries;

}

It's apparent there really are no issues even with code such as the following as it's easy to read

/// <summary>
/// 
/// I want to know how many orders from 1997 from shipping region of BC
/// where units in stock was under 80.
/// 
/// </summary>
public void Example1()
{ 
    NorthContext dc = new NorthContext();

    var query =
        from orders in dc.Orders
        // OrderDate is nullable
        where orders.OrderDate.Value.Year == 1997 && orders.ShipRegion == "BC"
        group orders by orders.OrderDate into orderGroup
        select new
        {
            OrderDate = orderGroup.Key,
            orderData = (
                    from ord in orderGroup
                    select new
                        {
                            CompanyName = ord.Customer.CompanyName,
                            ShipCountry = ord.ShipCountry,
                            OrderID = ord.OrderID,
                            productData = (
                                //
                                // utilize compound from clauses to trave to product data
                                // from parent which is order-details
                                //
                                from parent in orderGroup
                                from currentDetails in parent.Order_Details
                                where currentDetails.Product.UnitsInStock < 80
                                select new 
                                    { 
                                        ProductName = currentDetails.Product.ProductName, 
                                        UnitsInStock = currentDetails.Product.UnitsInStock 
                                    }
                                ).OrderBy(item => item.ProductName).ToList()
                        })
        };

    Console.WriteLine("count: {0} \n",query.Count());
   
    foreach (var groupItem in query)
    {

        // show companies for order details and since there may be a company multiple times
        // distinct is used to eliminate them
        string companies = string.Join(",", groupItem.orderData
            .Select(
                cust => cust.CompanyName)
            .ToArray()
            .Distinct());


        Console.WriteLine("{0} | {1}", 
            companies,
            groupItem.OrderDate.Value.ToShortDateString());

                                                                       
        foreach (var order in groupItem.orderData)
        {
            Console.WriteLine("   Country: {0}  OrderID: {1}", 
                order.ShipCountry,
                order.OrderID);
           
            order.productData.ForEach(
                (item) => Console.WriteLine("{0,40} in stock: {1}",
                    item.ProductName,
                    item.UnitsInStock));
            
        }

        Console.WriteLine();
    }

    Console.WriteLine();

}

But when we get into complex Lambda it can be messy to come back to the code to maintain if needed if one has not mastered Lambda but if we go back to LINQ most have an easier time comprehending LINQ syntax..

public IQueryable<CustomerOrderResult> RelationalWithTypedLambda()
{
    NorthContext dc = new NorthContext();

    IQueryable<CustomerOrderResult> query = dc.Orders.
                    Join(dc.Order_Details,
                    order => order.OrderID, orderDetails => orderDetails.OrderID,
                    (order, orderDetails) => new
                    {
                        OrderID = order.OrderID,
                        OrderDate = order.OrderDate,
                        ShipName = order.ShipName,
                        Quantity = orderDetails.Quantity,
                        UnitPrice = orderDetails.UnitPrice,
                        ProductID = orderDetails.ProductID
                    }).Join(dc.Products,
                            item => item.ProductID, p => p.ProductID,
                            (item, product) => new CustomerOrderResult2
                            {
                                OrderID = item.OrderID,
                                OrderDate = item.OrderDate,
                                ShipName = item.ShipName,
                                Quantity = item.Quantity,
                                UnitPrice = item.UnitPrice,
                                ProductName = product.ProductName
                            });

    return query;

}

In regards to view permissions, we use AD federated services and Azure tenants where a separate team handles permissions and we declarate methods to enforce security. Have not done any performance test in regards to query execution yet have not seen any poor responses.

I Believe one reason for out success comes from bringing on board two top notch web developers with well over 15 years of time developing web solutions while other teams don't fare so well if they learn as they go.

[SJWhiteley]

Kevininstructor, I'd rate this post, if I could.

[szlamany]

Kevininstructor, I'd rate this post, if I could.

@fd- why is this thread in Chit chat??

[FunkyDexter]

Actually I was just thinking that. The obvious answer is "because that's where SJW started it" but it does seem to be a serious enough discussion to merit being moved to elsewhere before Shaggy drowns it in puns . I think it's SJW's call to make, though, as it's his thread.

SJW, would you like me to move this to a more serious forum? It would be a reasonable fit in either Database, C# or General Dev (I'm leaning toward Database forum myself)

Edit> I tell a lie, it's Tyson's thread. In my defence, I've got Man Flu.

[TysonLPrice]

@fd- why is this thread in Chit chat??

Unless it is really giving someone heartburn please leave it where I started it. Thanks!

[szlamany]

I pop pills for heartburn - no problem here

[szlamany]

I had to lookup man flu - a UK'ism.

Thanks to google I'll never get that 5 seconds back again (used to be minutes!!)

The world is speeding up!

[techgnome]

I had to lookup man flu - a UK'ism.

Thanks to google I'll never get that 5 seconds back again (used to be minutes!!)

The world is speeding up!

Should we be lucky it wasn't Flu Man Chew?

-tg