Joining tables in Entity Framework cannot implicitly convert type error

Question

I've been stuck around for quite a long this evening trying to join to tables. I have table Challenges which has category Id and I want to join it with Category table to get actual title instead of having to do indexing in frontend which I am doing now.

public List<Challenges> GetChallenges()
{
        var list = new List<Challenges>();
        var queryable = list.AsQueryable();

        var challengesQ = (from ch in challengeContext.Challenges
                           join cc in challengeContext.ChallengeCategories
                                on ch.Category equals cc.Id 
                           select new {
                              id = ch.Id,
                              title = ch.Title,
                              createdAt = ch.CreatedAt,
                              daysNeeded = ch.DaysNeeded,
                              reward = ch.Reward,
                              difficulty = ch.Difficulty,
                              completedBy = ch.CompletedBy,
                              imgUrl = ch.ImgUrl,
                              subcategory = cc.Title,
                              category = cc.Title,
                              instructions = ch.Instructions
                          }
                           );
        DbSet<Challenges> challenges = challengeContext.Challenges;
        DbSet<ChallengeCategories> categories = challengeContext.ChallengeCategories;

        var q = challenges.Join(
            categories,
            ch => ch.Category,
            cc => cc.Id,
            (ch, cc) => new
            {
                id = ch.Id,
                title = ch.Title,
                createdAt = ch.CreatedAt,
                daysNeeded = ch.DaysNeeded,
                reward = ch.Reward,
                difficulty = ch.Difficulty,
                completedBy = ch.CompletedBy,
                imgUrl = ch.ImgUrl,
                subcategory = cc.Title,
                category = cc.Title,
                instructions = ch.Instructions
            });
        return challengesQ;
}

challengesQ and q are some of the things I've tried so far, yet I'm getting this or similar error:

CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<>' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?) challenge.EF

I know this is because I need IQueryable list or different way of executing query, but I couldn't come up with the right solution yet. Does anyone had similar problem ?

Answer 1

It appears your main problem is that you're attempting to pass out an IQueryable of an anonymous type instead of an IQueryable of Challenges.

Ideally, you'd have provided the definition of your POCO objects (if code-first) or the classes under the .edmx if you're using a design interface for your EF context. That way we can verify that you are actually mapping the join information to Challenge objects. As far as I can tell, you are doing so, and I'm going answer this under that assumption; it would be nice to be sure though.

I was able to define the following as a simple EF example:

 class Program
{
    static ChallengeContext Context = null;
    static void Main(string[] args)
    {
        try
        {
            var test = GetChallenges();
        }
        finally
        {
            Context.Dispose();
        }
    }
    public static IQueryable<Challenge> GetChallenges()
    {
        Context = new ChallengeContext();
        DbSet<Challenge> challenges = Context.Challenges;
        DbSet<ChallengeCategory> categories = Context.Categories;

       return challenges.Join(
            categories,
            ch => ch.Category,
            cc => cc.Id,
            (ch, cc) => new Challenge()
            {
                Id = ch.Id

            });

    }
}

internal class ChallengeContext:DbContext
{
    public DbSet<Challenge> Challenges { get; set; }
    public DbSet<ChallengeCategory> Categories { get; set; }
}

internal class ChallengeCategory
{
    public int Id { get; set; }
}

public class Challenge
{
    public int Id { get; set; }
    public int Category { get; set; }
}

Which suggests to me that the following should work:

public IQueryable<Challenges> GetChallenges()
{

    DbSet<Challenges> challenges = challengeContext.Challenges;
    DbSet<ChallengeCategories> categories = challengeContext.ChallengeCategories;

    return challenges.Join(
        categories,
        ch => ch.Category,
        cc => cc.Id,
        (ch, cc) => new Challenges()
        {
            id = ch.Id,
            title = ch.Title,
            createdAt = ch.CreatedAt,
            daysNeeded = ch.DaysNeeded,
            reward = ch.Reward,
            difficulty = ch.Difficulty,
            completedBy = ch.CompletedBy,
            imgUrl = ch.ImgUrl,
            subcategory = cc.Title,
            category = cc.Title,
            instructions = ch.Instructions
        });

}

Please let me know if this does not fix your problem.

Answer 2

You are returning anonymous type. You need to project to the actual type. Replace select new { with select new Category {

    var q = challenges.Join(
        categories,
        ch => ch.Category,
        cc => cc.Id,
        (ch, cc) => new {ch, cc}
        ).ToList().Select(x=>  new Category  
          {
            id = x.ch.Id,
            title = x.ch.Title,
            createdAt = x.ch.CreatedAt,
            daysNeeded = x.ch.DaysNeeded,
            reward = x.ch.Reward,
            difficulty = x.ch.Difficulty,
            completedBy = x.ch.CompletedBy,
            imgUrl = x.ch.ImgUrl,
            subcategory = x.cc.Title,
            category = x.cc.Title,
            instructions = x.ch.Instructions
        });

OR

         var challengesQ = ( from ch in challengeContext.Challenges
                      join cc in challengeContext.ChallengeCategories
                      on ch.Category equals cc.Id 
                      select new Category  {
                          id = ch.Id,
                          title = ch.Title,
                          createdAt = ch.CreatedAt,
                          daysNeeded = ch.DaysNeeded,
                          reward = ch.Reward,
                          difficulty = ch.Difficulty,
                          completedBy = ch.CompletedBy,
                          imgUrl = ch.ImgUrl,
                          subcategory = cc.Title,
                          category = cc.Title,
                          instructions = ch.Instructions
                      }
                       );