SQL conversion to LINQ - efficiency

Question

I have such a SQL (fragment)

var sSQL = @"DECLARE @HFID INT, @PLServiceID INT, @PLItemID INT
        SELECT @HFID = HFId FROM tblClaimAdmin WHERE ClaimAdminCode = @ClaimAdminCode AND ValidityTo IS NULL 
        SELECT @PLServiceID = PLServiceID FROM tblHF Where HFid = @HFID 
        SELECT @PLItemID = PLItemID FROM tblHF Where HFid = @HFID

I put this on LINQ

int? HFID;
int? PLServiceID;
int? PLItemID;

HFID = context.TblClaimAdmin
    .Where(c => c.ClaimAdminCode == model.claim_administrator_code && c.ValidityTo == null)
     .Select(x => x.Hfid).FirstOrDefault();

PLServiceID = context.TblHf
    .Where(h => h.HfId == HFID)
    .Select(x => x.PlserviceId).FirstOrDefault();

PLItemID = context.TblHf
    .Where(h => h.HfId == HFID)
    .Select(x => x.PlitemId).FirstOrDefault();

Previously, there was one query to the database.

Now I have 3

How does it work in reality? Is it much slower?

Can it be built better?

I'd suggest reading https://stackoverflow.com/questions/10021795/aggregating-multiple-queries-into-one-with-ef-like-this . — mjwills, Jun 12 '19 at 06:38
For me, the issue is the big picture (see rant above). If you are using Entity Framework then use SQL sparingly, e.g. where there isn't a solution in EF/code (overrriding PK insert for example) otherwise use C#, POCO and LINQ - IMHO. — Peter Smith, Jun 12 '19 at 07:18
make sure u execute multiple queries that should act as one in a transaction scope so if it errors it will roll back all three changes — JohnChris, Jun 12 '19 at 07:23
Why do you try to translate unefficient SQL into efficient LinQ? Do note that in your SQL you shoud `SELECT @PLServiceID = PLServiceID, @PLItemID = PLItemID FROM tblHF Where HFid = @HFID` to avoid one query. Edit, just checked again, your SQL should be only one sentece, so your LinQ also one. — Cleptus, Jun 12 '19 at 07:24
@bradbury9 I need to use LINQ to be compatible with many types of databases. SQL present does not support everyone — NetDev, Jun 12 '19 at 08:19
Consider using https://codereview.stackexchange.com next time you have code that works but are looking for ways of making it better. — Mark Cilia Vincenti, Jun 12 '19 at 08:41
Only your own tests will reveal if it is slower or faster, it depends on the data of the tables, its indexes, data statictics... The last two selects should make into only one, but I would personally would just make one linq query. I have just added an answer. — Cleptus, Jun 12 '19 at 13:05

score 1 · Answer 1 · answered Jun 12 '19 at 06:35

1

It will be slower, yes (and possibly not as consistent) because you have more round trips. Turn it into a UNION or a stored procedure with 3 OUT parameters. Then you can achieve it in one.

Personally, I'd use a SP. SQL to Linq questions really irk me :) There's already a solution, but let's make it "better" by using Linq

answered Jun 12 '19 at 06:35

LoztInSpace

5,584
1
15
27

1

I agree that LINQ isn't the right solution to all problems, but neither are SPs - ad-hoc SQL is also *just fine* in most cases (as long as correctly parameterized, etc) – Marc Gravell Jun 12 '19 at 06:57
Why throw out linq and go an entire different route with a Sproc? I'm pretty sure Linq also support inline queries, it also support union (trough `concat`) and it also support populating a tuple with the required values. – Dorus Jun 12 '19 at 07:16
1

I need to use LINQ to be compatible with many types of databases. SQL present does not support everyone – NetDev Jun 12 '19 at 08:10
@MarcGravell Yes ad-hoc SQL is fine. My point is around the situation when you have a working query and want to change it to LINQ for no apparent reason. – LoztInSpace Jun 13 '19 at 00:19

score 0 · Answer 2 · answered Jun 12 '19 at 13:02

The calls to FirstOrDefault() do execute the query to the database, so you are doing three separe queries.

If you are worried avoid performance, you should check the indexes and statistcs of the database. Using Linq or other ORM's sometimes the query executed is not what you would think of, because the ORM does interprete to query and executes its SQL behind the scenes.

It is very likely that instead doing three separate queries you should do one, for example:

from ca in context.TblClaimAdmin
join hf in context.TblHf on ca.Hfid equals hf.Hfid
where
    ca.ClaimAdminCode == model.claim_administrator_code 
    && ca.ValidityTo == null
select new { 
    ca.Hfid, 
    hf.PlserviceId, 
    hf.PlitemId 
}

SQL conversion to LINQ - efficiency

2 Answers2