CODEWITHSUNDEEP
c#sqlsql-serverlinq
prashant
prashant

Reputation: 2165

More complex queries in LINQ than SQL

I am new to LINQ queries. I have read/researched about all advantages of LINQ queries over SQL but i have one basic question why do we need to use these queries as i feel their syntax is more complicated than traditional sql queries?

For example look at below example for simple Left Outer Join

    var q=(from pd in dataContext.tblProducts 
           join od in dataContext.tblOrders on pd.ProductID equals od.ProductID into t 
           from rt in t.DefaultIfEmpty() 
           orderby pd.ProductID 
           select new 
           { 
               //To handle null values do type casting as int?(NULL int)
               //since OrderID is defined NOT NULL in tblOrders
               OrderID=(int?)rt.OrderID,
               pd.ProductID,
               pd.Name,
               pd.UnitPrice,
               //no need to check for null since it is defined NULL in database
               rt.Quantity,
               rt.Price,
           })
           .ToList();

Upvotes: 4

Views: 2006

Answers (3)

Andrew Gray
Andrew Gray

Reputation: 3790

So, the point of LINQ (Language Integrated Queries) is to provide easy ways of working with enumerable collections in executing memory. Contrast to SQL, which is a language for determining what the user gets from a set of data in a database.

Because of the SQL-like syntax, it's easy to confuse LINQ code with SQL, and think that they're 'alike' - they're really not. SQL gets a subset of data from a superset; LINQ is 'syntactic sugar' that hides common operations involving foreach loops.

For instance, this is a common programming pattern:

foreach(Thing thing in things)
{
    if(thing.SomeProperty() == "Some Value")
        return true;
}

...this is done rather easily in LINQ:

return things.Any(t => t.SomeProperty() == "Some Value");

The two code are functionally the same, and I'm pretty sure even compile to roughly the same IL code. The difference is how it looks to you.

You don't have to use LINQ; you can choose to use a standard foreach, and there are times, such as complex loops, where it is useful to do so. Ultimately it is a question of readability - my counter-question to you is, is the LINQ version of your foreach loop more, or less, readable than the original foreach loop?

If the answer is 'less', then I suggest converting it back to a foreach.

Upvotes: 1

Racil Hilan
Racil Hilan

Reputation: 25351

The main issue when people start using LINQ is that they keep thinking in the SQL way, they design the SQL query first and then translate it to LINQ. You need to learn how to think in the LINQ way and your LINQ query will become neater and simpler. For instance, in your LINQ you don't need joins. You should use Associations/Navigation Properties instead. Check this post for more details.

Upvotes: 1

Rao Ehsan
Rao Ehsan

Reputation: 776

I'm by no means an sql or a linq expert, I use them both.

There is a trend to either make linq into something bad or a silver bullet depending on what side are you.

You need to seriously consider your project requirements in order to choose. The choice is not mutually exclusive. Take what is good from them both .

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

I found that programmers used to Sql will have a hard time figuring out the tricks with LINQ. But programmers with Sql knowledge, but haven't done a ton of work with it, will pick up linq quicker.

Upvotes: 1

Related Questions