CODEWITHSUNDEEP
c#sqlentity-frameworklinq
SINETHEMBA PAULA
SINETHEMBA PAULA

Reputation: 31

Struggling to Convert SQL Query to LINQ in C#

How can I convert the below SQL query to LINQ. I'm new in LINQ and I'm struggling to understand this

select d.[TerminalId],
       d.[NameLocation]
      ,d.[MachineType]
      ,d.[LastEvent]
      ,d.[LastRequest]
      ,d.[CurrentLoadset]
      ,d.[CanGetCash]
      ,d.[CanDeposit]
      ,d.[CanPrintStatement]
      ,d.[CanCheckBalance]
      ,d.[CurrentMode]
      ,d.[LastEventDetail] from Device d
  left join DeviceBranch db on d.TerminalId = db.TerminalId 
  where db.BranchCode = '2200'
  and db.TerminalId in (SELECT DISTINCT d.TerminalId FROM Device)```

Upvotes: 1

Views: 67

Answers (2)

rachidait
rachidait

Reputation: 19

Can you try this please 

var result =
    (from d in Device 
    join db in DeviceBranch on d.TerminalId equals db.TerminalId into r
    from db in r.DefaultIfEmpty()
    where( db.BranchCode = "2200" && 
      (from de in Device select de.TerminalId).Distinct().Contains(d.TerminalId)
    )
    select d ).AsEnumerable();

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74595

I believe the query has several issues and actually runs the same as this:

  select d.[TerminalId],
       d.[NameLocation]
      ,d.[MachineType]
      ,d.[LastEvent]
      ,d.[LastRequest]
      ,d.[CurrentLoadset]
      ,d.[CanGetCash]
      ,d.[CanDeposit]
      ,d.[CanPrintStatement]
      ,d.[CanCheckBalance]
      ,d.[CurrentMode]
      ,d.[LastEventDetail] 
  from Device d
  inner join DeviceBranch db on d.TerminalId = db.TerminalId 
  where db.BranchCode = '2200'

An LINQ terms this would be:

 from d in device
 join db in devicebranch on d.TerminalId equals db.TerminalId
 where db.BranchCode == "2200"
 select new {
   d.TerminalId,
   d.NameLocation,
   d.MachineType,
   d.LastEvent,
   d.LastRequest,
   d.CurrentLoadset,
   d.CanGetCash,
   d.CanDeposit,
   d.CanPrintStatement,
   d.CanCheckBalance,
   d.CurrentMode,
   d.LastEventDetail 
 }

you'll need to swap device and devicebranch for your client side collection names.

If you're doing this as part of a db context query that already has navigation properties set up you can look at skipping the join (let the ORM do it) and have something like:

//if a device has multiple branches
var x = dbContext.Devices.Where(d => d.DeviceBranches.Any(db => db.BranchCode == "2200"));

//if a device has a single branch
var x = dbContext.Devices.Where(d => d.DeviceBranch.BranchCode == "2200");

//starting from the branch table, branch has a single device
var x = dbContext.DeviceBranches.Where(db => db.BranchCode == "2200").Select(db => db.Device);

//starting from the branch table, branch has multiple devices
var x = dbContext.DeviceBranches.Where(db => db.BranchCode == "2200").SelectMany(db => db.Devices);

Upvotes: 1

Related Questions