C# LINQ select from table and join multiple table and join a SQL query view in SQL server

I have a code that selecting from table and joining multiple tables and joining dbContext.Database.sqlQuery from view in sql server.

But it gives me this error

Unable to create a constant value of type 'ITManagement.Models.Employee'. Only primitive types or enumeration types are supported in this context.

My code

public JsonResult getEmployeeAsset(EmployeeController employee)
{ var employeeID = Request.QueryString["employeeID"]; var devices = (from asset in db.Devices where asset.EmployeeID == employeeID join brand in db.DeviceBrands on asset.Brand equals brand.ID join model in db.DeviceModels on asset.Model equals model.ID join type in db.DeviceTypes on asset.DeviceType equals type.ID join room in db.Rooms on asset.FullRoomCode equals room.FullCode //if device has last employee join lsEmp in db.Database.SqlQuery<LDAPUsers>("SELECT * FROM V_LDAP_Users") on asset.LastEmployeeID equals lsEmp.employeeID into lstEmp join sysUser in db.AspNetUsers on asset.sscUser equals sysUser.Id from lastEmployee in lstEmp.DefaultIfEmpty() select new { deviceID = asset.ID, SerialNumber = asset.SerialNumber, Type = type.Type, BrandName = brand.BrandName, ModelName = model.ModelName, MaccCode = asset.MaccCode, PONumber = asset.PONumber, WarrantyDate = asset.WarrantyDate.ToString(), MacAddress = asset.MacAddress, WIFIMacAddress = asset.WIFIMacAddress, PCName = asset.PCName, LastEmployee = asset.LastEmployeeID + "-" + lastEmployee.employeeName, Shared = asset.Shared == 1 ? "True" : "False", Location = room.RoomName, RecordedBy = sysUser.Name, requestID = (from request in db.StoreRequests where request.DeviceID == asset.ID && request.State == 1 && request.VoucherType == "ASD" orderby request.ID select request.ID).FirstOrDefault() }).DefaultIfEmpty(); return Json(new { assets = devices == null ? null : devices }, JsonRequestBehavior.AllowGet);
}

Your help please, thanks.

1 Answer

First of all, have you tried nested queries by commenting them out?

for example;

 //join lsEmp in db.Database.SqlQuery<LDAPUsers>("SELECT * FROM V_LDAP_Users") on asset.LastEmployeeID equals lsEmp.employeeID into lstEmp //requestID = (from request in db.StoreRequests where request.DeviceID == asset.ID && request.State == 1 && request.VoucherType == "ASD" orderby request.ID select request.ID).FirstOrDefault()

If there is no problem in these two, you can quickly find out which one is causing the problem by commenting the fields.

Tip: Also, more than 3 joins will affect your query performance. Try to split your queries as much as possible.