Can't fetch Navigation Property in Entity Framework

Question

I'm building a WPF MVVM Light app in Visual Studio 2015, using Entity Framework 6 to connect to a SQL Server 2008 R2 database. The two tables that need to be searched are: PersonnelTech and its child, PersonnelTechCostCenter.

A PersonnelTech can have any number of PersonnelTechCostCenter records, so the PersonnelTechId is a foreign key in the child table.

Here's the parent table:

CREATE TABLE [dbo].[PersonnelTech]
(
    [personnelTechId] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](95) NOT NULL,
    [email] [nvarchar](95) NOT NULL,
    [isActive] [bit] NOT NULL 
        CONSTRAINT [DF_PersonnelTech_isActive]  DEFAULT ((1)),
    [createdDate] [datetime] NOT NULL CONSTRAINT [DF_PersonnelTech_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_PersonnelTech] 
        PRIMARY KEY CLUSTERED ([personnelTechId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, 
                      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
)

And here's the child:

CREATE TABLE [dbo].[PersonnelTechCostCenter]
(
    [personnelTechCostCenterId] [int] IDENTITY(1,1) NOT NULL,
    [personnelTechId] [int] NOT NULL,
    [costCenter] [nvarchar](50) NOT NULL,
    [organizationalUnit] [nvarchar](50) NULL,
    [isActive] [bit] NOT NULL 
        CONSTRAINT [DF_PersonnelTechCostCenter_isActive]  DEFAULT ((1)),
    [createdDate] [datetime] NOT NULL 
        CONSTRAINT [DF_PersonnelTechCostCenter_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_PersonnelTechCostCenter] 
        PRIMARY KEY CLUSTERED ([personnelTechCostCenterId] ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PersonnelTechCostCenter] WITH CHECK 
  ADD CONSTRAINT [FK_PersonnelTechCostCenter_PersonnelTech] 
      FOREIGN KEY([personnelTechId])
      REFERENCES [dbo].[PersonnelTech] ([personnelTechId])

ALTER TABLE [dbo].[PersonnelTechCostCenter] 
   CHECK CONSTRAINT [FK_PersonnelTechCostCenter_PersonnelTech]

In my method, I'm trying to fetch the parent records and the children using LINQ, and populate some ObservableCollection view models with them:

using (var db = new MyEntities())
{
    var query = (from pt in db.PersonnelTeches
                 where pt.isActive.Equals(true)
                 orderby pt.name
                 select new PersonnelTechViewModel
                        {
                            PersonnelTechId = pt.personnelTechId,
                            Name = pt.name,
                            Email = pt.name,
                            IsActive = pt.isActive,
                            CreatedDate = pt.createdDate,
                            CostCenterVms = new ObservableCollection<PersonnelTechCostCenterViewModel>(pt.PersonnelTechCostCenters.Where(x => x.isActive)
                        .Select(
                        ptcc => new PersonnelTechCostCenterViewModel
                        {
                            PersonnelTechCostCenterId = ptcc.personnelTechCostCenterId,
                            PersonnelTechId = ptcc.personnelTechId,
                            CostCenter = ptcc.costCenter,
                            OrganizationalUnit = ptcc.organizationalUnit,
                            IsActive = ptcc.isActive,
                            CreatedDate = ptcc.createdDate
                        }).OrderBy(x => x.CostCenter).DefaultIfEmpty())
        }).ToList();
    return await Task.Run(() => new ObservableCollection<PersonnelTechViewModel>(query));
}

However, this blows up on the return statement and says:

Only parameterless constructors and initializers are supported in LINQ to Entities.

Other posts have said you need to turn the first part into its own .SELECT(), then do a .ToList() to realize it; finally, you'd do another .SELECT() to populate from the navigation property. I don't know how to this.

Answer 1

Okay First Off I don't know why you are projecting (doing a select) to seemingly go up a tree(from techs to their centers). EF should be doing all the joins for you through it's context operation. Let's make this more simple for example and say

I have a table tePerson that has fields: PersonId, FirstName, LastName, OrderId. This table has values

1   Brett   X 1
2   Emily   X 2
4   Ryan    Y 1
10  Mark    Z 1 

OrderId is a foreign Key to a table teOrder with only has two fields: OrderId and Description.

1   Shirt
2   Dress

When Entity Framework is setup you set up context that exists and then realizes your objects into a usable form from it's T4 template generally of generated POCO objects. Generally you see underneath a class named 'MyEntities.edmx' a context 'MyEntities.Context.tt' and 'MyEntities.tt'. Entities Context typically holds references to all the POCO Objects, while under the other your POCO's are created as similar .NET matches from what is in the database. However if you have a foreign key you should have entity create a relationship and do the hooking up for you. So in my Poco class for tePerson I see this:

public partial class tePerson
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Nullable<int> OrderId { get; set; }

    public virtual teOrder teOrder { get; set; }
}

It is important to note that that 'virtual teOrder' points to another POCO for me like this:

public partial class teOrder
{
    public teOrder()
    {
        this.tePersons = new HashSet<tePerson>();
    }

    public int OrderId { get; set; }
    public string Description { get; set; }

    public virtual ICollection<tePerson> tePersons { get; set; }
}

This means I can do something like this and traverse a tree structure using LINQ with EF:

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Timers;
using System.Data;
using System.Linq;
using System.Collections.ObjectMode;

static void Main(string[] args)
{
  using (var context = new TesterEntities())
  {
    var peopleWithOrderOfOne = context.tePersons.Where(x => x.OrderId == 1);

    // Go down to get the orders for Brett, Ryan, and Mark.  I am realizing an object that is a foreign key merely by selecting the complex object.
    // In this case x.teOrder is a POCO class mapped to another POCO class
    var observable = new ObservableCollection<teOrder>(peopleWithOrderOfOne.ToList().Select(x => x.teOrder).ToList());

    // display it
    observable.ToList().ForEach(x => Console.WriteLine($"{x.Description}"));

    //If you want to fully realize new objects you need to make them concrete by doing a select followed by a toList to materialize them, else they are not realized yet.
    // THis WILL NOT WORK:
    //var madeupPeopleAndOrders = context.tePersons
    //  .Select(x =>
    //    new tePerson
    //    {
    //      FirstName = x.FirstName,
    //      LastName = x.LastName,
    //      teOrder = new teOrder
    //      {
    //        OrderId = x.OrderId.Value,
    //        Description = x.teOrder.Description
    //      }
    //    });

    // THis WILL WORK:
    var madeupPeopleAndOrders = context.tePersons
      .ToList()
      .Select(x =>
        new tePerson
        {
          FirstName = x.FirstName,
          LastName = x.LastName,
          teOrder = new teOrder
          {
            OrderId = x.OrderId.Value,
            Description = x.teOrder.Description
          }
        });

    madeupPeopleAndOrders.ToList().ForEach(x => Console.WriteLine($"{x.FirstName} {x.LastName} {x.teOrder.Description}"));
  }

  Console.ReadLine();
}

In the end though you should not be re projecting values (using selects) of the same objects you already have. Unless you are limiting scope for speed (say one of your columns is varchar(max) and has notes) and you want to limit the set returned from a table. Then do it. Else if you want the objects as they are, that is already hooked up for you. And if you have lazy loading(which should be on by default and is an option in EF) you are getting ALL the data from the joined tables by merely doing something like x.(childobject).(childchildobject).(propertyofChild). So if I wanted a grandchild object of something to present back out I could just do this:

context.tableName.toList().Select(x => x.childobject.itschildobject.value).ToList()

I just realized an object with making a 'ToList()', then I traversed a structure with a lambda 'select' to get it's child objecs and their child objects via foreign keys, then realized that structure with a 'toList' to get them.