CODEWITHSUNDEEP
c#sqllinq
Hugo Silva
Hugo Silva

Reputation: 352

c# linq to entities using method based queries - trying to select where the object appears only once

i have got this table that relates the Table hardware with a table Process.. this table is called processHardware. this table is discribed by: IDProcessHardware IDProcess IDHardware State

the field state can have 3 states (1-Insert, 2-Remove,3-Substitute).. so i can i have this:

IDProcessoHardware  IDProcesso  IDHardware State

  1                  10          1        1

  2                  10          2        1

  3                  10          1        2

what this tell me is that the hardware with id 1 was insert on the process with the id 10 then the user insert the hardware with id 2 on the process with the id 10, and the it remove the hardware with the id 1 from the process with the id 10

by giving the id of the process i want to get the id of the hardware that were insert, this is, the id of the hardware that were remove.. so in this case the record that i will get is record number 2..because was insert, but was not removed.. after getting the ids from this table i need to relate the ids with the table hardware, this table is described by idhardware, serial number, description..

i was using linq method base..

and this was something that i did, but didnt go further after this..

var ProcessoHardware = from procHardware in db.ProcessoHardwares
                       where procHardware.Rem == 0 && procHardware.IDProcesso == IDProcesso
                       group procHardware by procHardware.IDHardware into g
                       select new { IDHardware = g.Key, count = g.Count() };

the query above didnt work for me... so i want to get the records that appears only once on the table, and then relate the ids that were obtained from this query and get the info about those ids like, serial number, description(these fields are on a table called Hardware).

thanks in advance..

in sql i manage to do the query ..

SELECT * 
FROM 
(SELECT IDHardware ,COUNT(IDHardware) nu 
FROM dbo.ProcessoHardware 
WHERE IDProcesso=47 
Group By IDHardware) T WHERE nu=1

how do i pass this to linq?

Upvotes: 0

Views: 135

Answers (1)

Andy Nichols
Andy Nichols

Reputation: 3002

Firstly your SQL statement would be clearer if you used the having clause so it becomes

SELECT IDHardware, COUNT(IDHardware) nu 
FROM dbo.ProcessoHardware 
WHERE IDProcesso=47 
GROUP BY IDHardware
HAVING COUNT(IDHardware) = 1

secondly, your SQL statement doesn't mention a field called Rem, but your LINQ states where procHardware.Rem == 0. I'm going to assume that you need to keep that filter. If so then all you need to do is add a where clause to count your group, g. Try the following

var ProcessoHardware = from procHardware in db.ProcessoHardwares
                       where procHardware.Rem == 0 && procHardware.IDProcesso == IDProcesso
                       group procHardware by procHardware.IDHardware into g
                       where g.Count() == 1
                       select new { IDHardware = g.Key, count = g.Count() };

although the literal transformation of your statement (without the Rem and hard coded ID of 47) to LINQ would be

var ProcessoHardware = from procHardware in db.ProcessoHardwares
                       where procHardware.IDProcesso == 47
                       group procHardware by procHardware.IDHardware into g
                       where g.Count() == 1
                       select new { IDHardware = g.Key, count = g.Count() };

Upvotes: 1

Related Questions