CODEWITHSUNDEEP
asp.netsql-serverasp.net-mvcentity-frameworkjoin
peanut
peanut

Reputation: 283

Entity Framework Query Join issue

I'm using an AJAX query to call a function that references a model.

So far it returns details to a user table, which all works fine. The problem is, I have a one to many relationship to another table.

Person - PersonId goes to joining table - personId linked to potentially multiple colourIds - colourId links to colour table.

So three tables - person, favourite colour and colour are involved.

I want to include a join in my original query but I'm having difficulty. The query:

TechTestEntities testTechObj = new TechTestEntities();
                     var Result = from p in testTechObj.People
                     join fp in testTechObj.FavouriteColours on p.PersonId equals fp.PersonId
                     join c in testTechObj.Colours on fp.ColourId equals c.ColourId
                     select p;

When I run this I get the error that 'The entity type FavouriteColours is not part of the model for the current context.'

I have also added FavouriteColours to the model like so:

 public virtual DbSet<FavouriteColours> FavouriteColours { get; set; }

All the tables should be included in the ADO model, so I'm not sure what the problem is and how to retrieve the colour names through a join.

Edit:

Model code

    namespace techTest4
        {
        using System;
        using System.Data.Entity;
        using System.Data.Entity.Infrastructure;
        using techTest4.Models;

    public partial class TechTestEntities : DbContext
    {
        public TechTestEntities()
            : base("name=TechTestEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<Colour> Colours { get; set; }
        public virtual DbSet<Person> People { get; set; }
        //public virtual DbSet<FavouriteColours> FavouriteColours { get; set; }
    }
}

Upvotes: 0

Views: 135

Answers (1)

J.N.
J.N.

Reputation: 536

I had to guess what your classes look like, but take a look at this code: ( https://dotnetfiddle.net/TVqzse )

This snippet is the most interesting for you:

var favoriteColours = people.SelectMany(p => p.FavouriteColours);

foreach(var favoriteColour in favoriteColours) {    
    System.Console.WriteLine(favoriteColour.Color.ColorName);
}

This uses LINQ to extract the favourite colours of all people, and you should be able to do exactly the same in Entity Framework.

Upvotes: 1

Related Questions