In entity framework 6 database first when using stored procedures I simply do all of my left outer joins and inner joins and select any specific navigation properties in my query with names that match my extended property names. Then in my data library I extend the auto-generated classes with any Navigation properties that the stored procedure is setting so I can do something like this: (in this example I simply set the CustomerName property which is a property I manually added to CustomerContact in my extension class (i.e.)
public partial class CustomerContact {
public string CustomerName { get; set; }
}
public static IEnumerable<CustomerContact> GetList(int customerId = null)
{
using (var context = new LowthersContext())
{
var procList = context.GetCustomerContactsProc(customerId);
var ccList = (from cc in procList
select new Material()
{
Id = cc.Id,
FirstName = cc.FirstName,
LastName = cc.LastName,
Email = cc.Email
// navigation properties from joins
CustomerName = cc.CustomerName // this property is added in partial class
}).ToList();
return ccList;
}
}
The above works fine, and it is bit of an oldschool technique using stored procedures, but I want to convert some of my simple stored procs to use Linq to SQL instead. Here is my attempt and I am getting an error:
The entity or complex type 'Data Model.CustomerContact' cannot be constructed in a LINQ to Entities query.
var cList = (from cc in context.CustomerContacts
join c in context.Customers on cc.CustomerId equals c.Id
join cl in context.CustomerLocations on c.LocationId = cl.Id
where (customerId == null || cc.CustomerId == customerId)
select new CustomerContact
{
Id = cc.Id,
FirstName = cc.FirstName,
LastName = cc.LastName,
Email = cc.Email,
// navigation properties
CustomerName = c.Name
ContactLocationName = cl.LocationName
}).ToList();
return cList;
I really want to ignore the navigation properties that Entity Framework auto generates for me and set only the individual properties as needed rather than whole objects for performance.
