Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hallo, I am new in mvc and linq. I've 2 table in relation many to many: 1)employe 2)device
Selecting an employe I'd like to see his devices:

-Marco -Franco
Selecting Marco should appear the devices associated to him.


In sql I'd like this:
select Devices.nome from Devices where Devices.id IN(select DeviceID from ImpiegatoDevices where impiegatiID=id);




What should I do?

Below my code

Thank you

Ale

What I have tried:

My code (not work):

Controller:
C#
public ActionResult VediDevice(int id)
{
    Impiegato impi = new Impiegato();

    List<Device> ListDevice = new List<Device>(); 

    using (DatabaseMVCCOntrolsEntities db = new DatabaseMVCCOntrolsEntities())
    {
        ListDevice= db.Devices.Where(u => db.ImpiegatiDevice.Any(m => m.impiegatiID == id)).ToList();  //<---???

        //or

        ListDevice= db.Devices.ToList().Where(u => u.item.Any(m => m.impiegatiID == id)).ToList();   //<---????

    }

    return View(ListDevice);
}

Device class, I added:
C#
List<ImpiegatiDevice> item{get;set;}


VediDevice.cshtml:
Razor
@model List<mvccontroltoolkit.models.device>
@{
    ViewBag.Title = "VediDevice";
}

@foreach (var item in Model)
{
}

<table class="table table-striped  table-condensed">
<tbody>
<tr>
    <th>NOME</th>
</tr>
<tr>
    <td>@Html.DisplayFor(modelItem => item.nome)</td>
</tr>
</tbody>
</table>

Not work.

What should I do?

Thank you

Ale
Posted
Updated 28-Nov-19 23:16pm
v3
Comments
Mehul M Thakkar 28-Nov-19 6:14am    
What is not working? did you get any error?
AleLo2014 28-Nov-19 7:54am    
using ListDevice = db.Devices.ToList().Where(u => u.itemma.Any(m => m.impiegatiID == id)).ToList();
i get this error:
System.ArgumentNullException: 'Il valore non può essere null.
Nome parametro: source'
Mehul M Thakkar 29-Nov-19 3:25am    
It says Parameter Name: source, it means the devices you are retrieving is null. first check db.Devices.ToList() is null then do not apply Where clause.
AleLo2014 29-Nov-19 3:32am    
In sql I'd like this:
select Devices.nome from Devices where Devices.id IN(select DeviceID from ImpiegatoDevices where ImpiegatoDevices.impiegatiID=id);
Mehul M Thakkar 29-Nov-19 3:35am    
first you execute ListDevice = db.Devices.ToList(); and see what you are getting in ListDevice.

I'm quite confused since, for a many to many relationship, you need a third table.
SQL
-- SQL pseudocode

TABLE Employee
(
   EmployeeId int PRIMARY KEY IDENTITY,
   EmployeeName nvarchar
)

TABLE Device
(
   DevideId int PRIMARY KEY IDENTITY,
   DeviceName nvarchar
)

TABLE EmployeeDevice
(
   EmployeeId int FOREIGN KEY REFERENCES (Employee.EmployeeId),
   DeviceId int FOREIGN KEY REFERENCES (Device.DeviceId)
)
The EmployeeDevice table represents the many-to-many relationship. Then, if you want all devices associated to Marco, you can write
SQL
SELECT
  d.DeviceId
 ,d.DeviceName
FROM Device d
INNER JOIN EmployeeDevice ed on ed.DeviceId = d.DeviceId
INNER JOIN Employee e on e.EmployeeId = ed.EmployeeId
WHERE e.EmployeeName = N'Marco'
Then in Linq:
C#
List<Device> devices = context.Devices.Where
(
   d => d.DeviceEmployees.Any
   (
      de => de.Employee.EmployeeName.Equals("Marco")
   )
);
 
Share this answer
 
Comments
AleLo2014 29-Nov-19 4:06am    
I started from the database not from code.

I've got the id of the employe, therefore i can do the subquery or join 2 table:
Devices and ImpiegatiDevice.

I've written, but not work:
ListDevice= db.Devices.Where(u => db.ImpiegatiDevice.Any(m => m.impiegatiID == id)).ToList();
phil.o 29-Nov-19 4:12am    
Sorry, "not working" is not precise enough.
Maybe
ListDevice = db.Devices.Where(u => u.ImpiegatiDevices.Any(m => m.impiegatiID == id)).ToList();

All I can see is that you do not use the u variable in your subquery.
AleLo2014 29-Nov-19 5:15am    
Hi Phil, that work!!!!!!!! thank you so mutch!!!!!!!!!!
phil.o 29-Nov-19 5:23am    
Glad to have helped. May I ask you to formally mark your question as answered, so that it leaves the queue of unanswered questions?
AleLo2014 29-Nov-19 6:20am    
Off sure but how can I do? :-). I can't find the button.
Sorry, i'm just clod.
Phil found the solution (thank you so mutch!!!):
ListDevice = db.Devices.Where(u => u.ImpiegatiDevices.Any(m => m.impiegatiID == id)).ToList();
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900