mvc select second table data using linq

shozo lojacoono

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.

My code (not work):

Nel controller:

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);

}

In device.cs I added:

List<ImpiegatiDevice> item{get;set;}

VediDevice.cshtml:

@model List<MVCControlToolkit.Models.Device>

@{
ViewBag.Title = "VediDevice";
}

VediDeviceh2>

<table class="table table-striped table-condensed">

NOME
th>

tr>

@foreach (var item in Model)
{

@Html.DisplayFor(modelItem => item.nome)
td>

tr>
}

table>

Not work.

What should I do?

Thank you

Ale

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

shozo lojacoono6y ago
Hi, thank you to everybody.
I found the solution and i want to share:
ListDevice = db.Devices.Where(u => u.ImpiegatiDevices.Any(m => m.impiegatiID == id)).ToList();
0
shozo lojacoono6y ago
Hi Ashutosh,
give me this error: System.NotSupportedException: 'Il metodo 'First' può essere utilizzato solo come operazione di query finale. In questa istanza utilizzare il metodo 'FirstOrDefault'.'

In sql I'd like this:
select Devices.nome from Devices where Devices.id IN(select DeviceID from ImpiegatoDevices where ImpiegatoDevices.impiegatiID=id);
0
Ashutosh Gupta6y ago
ok, let's try this. you need to make use of bridge(ImpiegatoDevices) class which contains both deviceid and  impiegatiID,

ListDevice=(from st in db.Devices
               where db.ImpiegatiDevice.FirstorDefault(e=>e.DeviceID == st.id) != null                        && db.ImpiegatiDevice.First(e=>e.DeviceID == st.id).impiegratiID== id
               select st).ToList();

this will get you only devices belogs to input emp id.
0
shozo lojacoono6y ago
Hi Ashutosh, my 3 classes:

1) public partial class Impiegato
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Impiegato()
{
this.ImpiegatiDevice = new HashSet();
}

public int Id { get; set; }
public string nome { get; set; }
public string cognome { get; set; }
public string email { get; set; }
public Nullable livello { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection ImpiegatiDevice { get; set; }
}

2)
public partial class Device
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Device()
{
this.ImpiegatiDevice = new HashSet
();
}

public int Id { get; set; }
public string nome { get; set; }
public string descrizione { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection
ImpiegatiDevice { get; set; }
public List itemma { get; set; }
}

3) the bridge:
public partial class ImpiegatoDevices
{
public int Id { get; set; }
public Nullable impiegatiID { get; set; }
public Nullable DeviceID { get; set; }

public virtual Device Device { get; set; }
public virtual Impiegato Impiegati { get; set; }

public List itemDevLis { get; set; }
}

0
Ashutosh Gupta6y ago
how does device and employ has manay to many relationship, it does not reflect into your device class. is it at schema level? can you add your schema of both tables here.
0
shozo lojacoono6y ago
Hi Ashutosh,

My device.cs:

public partial class Device
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Device()
{
this.ImpiegatiDevice = new HashSet
();
}

public int Id { get; set; }
public string nome { get; set; }
public string descrizione { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection
ImpiegatiDevice { get; set; }
public List itemma { get; set; }
}

0
Ashutosh Gupta6y ago
i assume that device.cs contains empID as int property.
ListDevice= db.Devices.Where(u => db.ImpiegatiDevice.Any(m => m.impiegatiID == u.ID) && u.empID == id).ToList();
0
shozo lojacoono6y ago
Hi Dhara, using ListDevice=(from st in db.Devices where db.ImpiegatiDevice.Any(e=>e.impiegratiID==st.id) select st).ToList();
i've no error but I've got always the same result: every employe have the same list of device.....
0
Dhara Patel6y ago
hi,
Try this query:

ListDevice=(from st in db.Devices where db.ImpiegatiDevice.Any(e=>e.impiegratiID==st.id) select st).ToList();
0
shozo lojacoono6y ago
Hi Dhara,
I get this error

0
shozo lojacoono6y ago
Hi Dhara, using
ListDevice = from sate in db.Devices where (db.ImpiegatiDevice.Any(e => e.impiegatiID == id)) select sate;

I get this error:
"
Errore CS0266 Non è possibile convertire in modo implicito il tipo 'System.Linq.IQueryable' in 'System.Collections.Generic.List'. È presente una conversione esplicita. Probabilmente manca un cast. MVCControlToolkit "

0
Dhara Patel6y ago
Hi, Change linq query: ListDevice=from st in Devices where ImpigratiDeavice.Any(e=>e.impiegratiID=st.id)) select st;
0

mvc select second table data using linq

VediDeviceh2><table class="table table-striped table-condensed"> NOME th> tr> @foreach (var item in Model) { @Html.DisplayFor(modelItem => item.nome) td> tr> }table>

VediDeviceh2>

<table class="table table-striped table-condensed">

NOME
th>

tr>

@foreach (var item in Model)
{

@Html.DisplayFor(modelItem => item.nome)
td>

tr>
}

table>