CODEWITHSUNDEEP
c#performancejoinlinq-to-sql
Denis
Denis

Reputation: 3757

Typical Linq-to-SQL Join situation - best practices?

It is possible to write same 'Join' thing in few ways using Linq-to-SQL. Is it any performance difference? What are best practices?

Either:

database.Orders.
    Where(o => o.S == s).
    Join(database.OrderEvents.Where(e => e.B == b),
        o => o, e => e.Order, (o, e) => o).
    ToArray();

Or:

database.Orders.
    Where(o => o.S == s).
    Join(database.OrderEvents,
        o => o, e => e.Order, (o, e) => new { O = o, E = e }).
    Where(a => a.E.b == b).
    Select(a => a.O).
    ToArray();

Or:

database.Orders.
    Join(database.OrderEvents,
        o => o, e => e.Order, (o, e) => new { O = o, E = e }).
    Where(a => a.O.S == s && a.E.b == b).
    Select(a => a.O).
    ToArray();

Upvotes: 1

Views: 577

Answers (2)

Denis
Denis

Reputation: 3757

Well, I ran 1 and 3 versions of the code under a profiler. SQL query is the same except an order of params and conditions in WHERE clause. So, performance is the same. What about readability?

Upvotes: 0

MichaC
MichaC

Reputation: 13380

You cannot simply say which query does a better job. To do so, you have to trace the generated SQL statement and analyse it.

All three linq statements might at the end produce pretty similar SQL statements, if so, you will not see any performance difference.

If the linq statements do actually generate different SQL statements you can check the performance with sql tools, looking at the query plan etc.

Maybe you will still not see any performance differences because SQL Server optimizes the query plan for you...

So no, there is usually no best practice except from the fact that you have to do the job of analyzing the generated queries if you want to fine tune your application or if you want to figure out performance issues...

Upvotes: 1

Related Questions