Entity Framework Core Error when Adding a Join

Question

how come this works:

var query = _context.Routing_Tool
            .Where(rt => rt.Id == id)
            .FirstOrDefault();

return query;

but this does not

var query = _context.Routing_Tool                       
     .Join(_context.Routing_Tool_Prioritization_Matrix,
      rt => rt.Id,
      rp => rp.RoutingToolId,
      (rt, rp) => new 
             { 
               Id = rt.Id,
               Title = rt.Title,
               LoeName = rp.LoeName,
              }
        )
         .Where(rt => rt.Id == id)
         .FirstOrDefault();

 return query;

instead, I get and implicitly convert type error After I try to join a table. Help is appreciated

The whole new method - Including the old one I was working on for reference.

public Routing_Tool GetItemsInitById(int id)
        {


            //var query = (from rt in _context.Set<Routing_Tool>()
            //              join rp in _context.Set<Routing_Tool_Prioritization_Matrix>()
            //                  on rt.Id equals rp.RoutingToolId into grouping

            //             from rp in grouping.DefaultIfEmpty()
            //             select new Routing_Tool { 
            //                 Id = rt.Id,
            //                 Title = rt.Title,
            //                 Classification = rt.Classification,
            //                 MainPOC = rt.MainPOC,
            //                 RequestingDirectorate = rt.RequestingDirectorate,
            //                 IsEnduring = rt.IsEnduring,
            //                 IsApproved = rt.IsApproved,
            //                 IsAssociated = rt.IsAssociated,
            //                 DirectingRequirement = rt.DirectingRequirement,
            //                 RequirementDescription = rt.RequirementDescription,
            //                 RequestType = rt.RequestType,
            //                // LoeName = rp.LoeName,
            //                // LoePriority = rp.LoePriority,
            //             }

            //             ).FirstOrDefault();

            // return query;
            var query = _context.Routing_Tool
                         .Join(_context.Routing_Tool_Prioritization_Matrix,
                                    rt => rt.Id,
                                    rp => rp.RoutingToolId,
                                    (rt, rp) => new
                                    { 
                                        Id = rt.Id,
                                        Title = rt.Title,
                                        
                                    }
                          )
                         .Where(rt => rt.Id == id)
                         .FirstOrDefault();

            return query;

        

            //return _context.Routing_Tool.FirstOrDefault(p => p.Id == id);
        }

it does not allow to return of the value. return query; and gives a casting error message. Compiler Error CS0029. — Sergio Rodriguez
– Sergio Rodriguez, Commented May 6, 2021 at 12:22
Why you don't use the variant that is working? I much more readable and because of this I am sure it has better performance too — Serge
– Serge, Commented May 6, 2021 at 14:05

feihoa · Accepted Answer · 2021-05-06 12:32:04Z

1

In the first case you the query variable is of IQueryable<Routing_Tool> type, which is right, you return it and everything is fine. In the second case you return anonymous type and you have two options here:

Create a model for that type (with Id, Title and LoeName properties) and return "IQueryable< MyType >" instead
Return "Routing_Tool" from the expression ((rt, rp) => rt), which has no sense

answered May 6, 2021 at 12:32

feihoa

5174 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sergio Rodriguez Over a year ago

Thanks for helping. I created the model and still gives me issues. might it be because of the version 2.0 core? I have read and watched videos for days and can't get a solution for this thing.

feihoa Over a year ago

No, it has nothing to do with ef core. Post the whole method.

Sergio Rodriguez Over a year ago

Post updated. I noticed when I add IQueryable breaks the single Where().

feihoa Over a year ago

public Routing_Tool GetItemsInitById(int id) You are not returning Routing_Tool here. public object GetItemsInitById(int id) will solve the compile error, but won't solve the problem.

Sergio Rodriguez Over a year ago

all I want to do is to join the priority_matrix table to the main routing_tool table by a FK Id and return the results.

Sergio Rodriguez · Accepted Answer · 2021-05-06 19:04:16Z

I had to modify the Models for both tables by adding a collection, a new public method, a new select query:

 public Routing_Tool GetItemsInitById(int id)
        {
            var initPage = _context.Routing_Tool
                .Include(pub => pub.Routing_Tool_Prioritization_Matrices)
                .Where(rt => rt.Id == id)
                .FirstOrDefault();

            return initPage;
        }

routing tool model:

public class Routing_Tool
    {
        public Routing_Tool()
        {
            Routing_Tool_Prioritization_Matrices = new HashSet<Routing_Tool_Prioritization_Matrix>();
        }

        [Key]
        [Required]
        public int Id { get; set; }

        [MaxLength(255)]
        [Required]
        public string Title { get; set; }

        [MaxLength(255)]
        [Required]
        public string Classification { get; set; }

        [MaxLength(255)]
        [Required]
        public string MainPOC { get; set; }

        [MaxLength(10)]
        [Required]
        public string RequestingDirectorate { get; set; }

        [MaxLength(1)]
        [Required]
        public int IsEnduring { get; set; }

        [MaxLength(1)]
        [Required]
        public int IsApproved { get; set; }

        [MaxLength(1)]
        [Required]
        public int IsAssociated { get; set; }

        [Required]
        public string DirectingRequirement { get; set; }

        [Required]
        public string RequirementDescription { get; set; }
        
        [Required]
        public string RequestType { get; set; }
        public virtual ICollection<Routing_Tool_Prioritization_Matrix> Routing_Tool_Prioritization_Matrices { get; set; }

    }

Dimensional table with FK:

public class Routing_Tool_Prioritization_Matrix
    {
        [Key]
        [Required]
        public int Id { get; set; }
        public string LoeName { get; set; }
        public string LoePriority { get; set; }
        public int RoutingToolId { get; set; }
        [ForeignKey("RoutingToolId")]
        public virtual Routing_Tool Routing_Tool { get; set; }
    }

Collectives™ on Stack Overflow

Entity Framework Core Error when Adding a Join

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related