EF LINQ method for related entities

Question

I'm trying to write a LINQ query using extension methods with EF code first to tranverse through 3 related entities. The CpeDefinition & PaperMachine entities have many-many relationships with the Tag entity.

I have 2 static inputs for this query, 1 for Cpe Id and another a Tag Name.

I want to get the CpeDefinition record on the user selected id, then for that Cpe record, I want to get all the matching Tags mapped to that Cpe, then I want to filter those resulting tags by the user inputted tag name plus a colon. Finally from those resulting tags, I want to get the PaperMachine records mapped to those Tags.

    public class Tag {
        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<CpeDefinition> CpeDefinitions { get; set; }
        public virtual ICollection<PaperMachine> PaperMachines { get; set; }        
    }

    public class CpeDefinition
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public virtual ICollection<Tag> Tags { get; set; }
    }

    public class PaperMachine
    {
        public int Id { get; set; }
        public string Type { get; set; }
        public string Plant { get; set; }
        public string Line { get; set; }
        public virtual ICollection<Tag> Tags { get; set; }
    }

    modelBuilder.Entity<PaperMachine>()
                .HasMany<Tag>(p => p.Tags)
                .WithMany(t => t.PaperMachines)
                .Map(pt => {
                    pt.MapLeftKey("PaperMachineId");
                    pt.MapRightKey("TagId");
                    pt.ToTable("PaperMachinesTags");
                });

    modelBuilder.Entity<CpeDefinition>()
                .HasMany<Tag>(d => d.Tags)
                .WithMany(t => t.CpeDefinitions)
                .Map(dt => {
                    dt.MapLeftKey("CpeDefinitionId");
                    dt.MapRightKey("TagId");
                    dt.ToTable("CpeDefinitionsTags");

Initially I had this query, which almost gets me there, but it is not filtering the tags down by the matching CpeDefinition (it returns all papermachines that match the user inputted tag, but not the user selected Cpe:

string tag = "SiteA";

var taggedPaperMachines = db.Tags
    .Where(t => t.Name.Contains(String.Concat(tag, ":")))
    .Select(p => new
    {
        p.PaperMachines.FirstOrDefault().Id,
        p.PaperMachines.FirstOrDefault().Line,
        p.PaperMachines.FirstOrDefault().Plant,
        p.PaperMachines.FirstOrDefault().Type
    }).ToList()
    .Select(pm => new PaperMachine
    {
        Id = pm.Id,
        Line = pm.Line,
        Plant = pm.Plant,
        Type = pm.Type
    });  

I tried this to get the Cpe and Tags together, but I'm getting a null error which I don't understand why since I know there is a Cpe with Id = 1 in the database table:

The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type

int CpeId = 1;
string TagName = "SiteA";

var temp1 = db.CpeDefinitions
    .Where(cd => cd.Id == CpeId)
    .Select(cd => new
    {
        cd.Tags.FirstOrDefault().Id,
        cd.Tags.FirstOrDefault().Name
    }) 
    .ToList()
    .Select(t => new Tag
    {
        Id = t.Id,
        Name = t.Name
    });    

In a perfect world, I'd like to have all this logic wrapped in a single query, but at this point a don't mind taking the results of the 2nd query and pushing it into the 1st query.

Answer 1

Very easy :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataBase db = new DataBase();
            string tag = "SiteA";

            var query = (from t in db.Tags
                         join c in db.CpeDefinitions on t.Id equals c.Id
                         select new { tag = t, definition = c }
                        ).ToList();

            List<PaperMachine> paperMachines = query.Where(x => x.tag.Name == (tag + ":"))
                .SelectMany(x => x.tag.PaperMachines).ToList(); 

        }
    }
    public class DataBase
    {
        public List<Tag> Tags { get; set; }
        public List<CpeDefinition> CpeDefinitions { get; set; }
        public List<PaperMachine> PaperMachines { get; set; }
    }
    public class Tag
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<CpeDefinition> CpeDefinitions { get; set; }
        public virtual ICollection<PaperMachine> PaperMachines { get; set; }
    }

    public class CpeDefinition
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public virtual ICollection<Tag> Tags { get; set; }
    }

    public class PaperMachine
    {
        public int Id { get; set; }
        public string Type { get; set; }
        public string Plant { get; set; }
        public string Line { get; set; }
        public virtual ICollection<Tag> Tags { get; set; }
    }
}