0

I have three tables (simplified example for this issue):

Database diagram

Models are generated using EntityFramework Database-First approach.

OwnerModel

public partial class Owner
{
    public Owner()
    {            
       this.OwnerDogMapper= new HashSet<OwnerDogMapper>();
    }
    public string OwnerId { get; set; }
    public string OwnerName { get; set; }
    public virtual ICollection<OwnerDogMapper> OwnerDogMapper{ get; set; }
}

DogTable

public partial class Dog
{
    public Dog()
    {
        this.OwnerDogMapper= new HashSet<OwnerDogMapper>();
    }
    public string DogId { get; set; }
    public string DogName { get; set; }
    public virtual ICollection<OwnerDogMapper> OwnerDogMapper{ get; set; }
}

and the mapping table: OwnerDogMapper

public partial class OwnerDogMapper
{
    public string OwnerId { get; set; }
    public string DogId { get; set; }
    public virtual Owner Owner { get; set; }
    public virtual Dog Dog { get; set; }
}

Now, I'm trying to join these three tables and get the OwnerName and DogName when an OwnerId is passed. Here is my query:

var output = await (from o in db.Owner
                    join odm in db.OwnerDogMapper on o.OwnerId equals odm.OwnerId
                    join d in db.Dog on odm.DogId equals d.DogId
                    where o.OwnerId == '01'
                    select new { o.OwnerName, d.DogName }
                   ).ToListAsync();

But it throws an exception:

Exception: The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.

Type '<>f__AnonymousType2`2[System.String,System.String]' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute. If the type is a collection, consider marking it with the CollectionDataContractAttribute. See the Microsoft .NET Framework documentation for other supported types.

The DataLayer returns Models to the BusinessLayer where the DTO mapping is done using AutoMapper. There aren't any DataContracts in the Models generated by the EF. Also, so far in the project, I have stayed away from passing DTOs directly from the DataLayer.

If I use Lambda expression which is similar to the one mentioned on Entity Framework Join 3 Tables

var output = await db.Owner
                   .Where(o => o.OwnerId == "01")
                   .Include(odm => odm.OwnerDogMapper.Select(d => d.Dog))
                   .ToListAsync();

However, in my case I don't have any relationship between the [Owner] and [Dog] Table. With this lamba query, it goes to an infinite loop and I get an "StackOverflowException" :D -

"make sure you do not have an infinite loop or infinite recursion

Is there something fundamentally wrong with the table mapping or the models generated? Or my query is not right?

Community
  • 1
  • 1
sandiejat
  • 2,552
  • 19
  • 24
  • 1
    I think there is something fundamentally wrong with model generation in EF. Generally if you have many-to-many relationship in a table which is having only two columns as foreign key with composite primary key than that particular table won't show in designer. The model classes will have the navigation collection properties of each of the relation model. So I believe in your designer *OwnerDogMapper* should not be in edmx. http://stackoverflow.com/a/35527376/1433093 – Kundan Singh Chouhan Feb 25 '17 at 07:29
  • @KundanSinghChouhan - Thanks for the post. OwnerDogMapper is in the edmx. I'm able to write the join query and the IDE picks it up too. – sandiejat Feb 28 '17 at 16:31

1 Answers1

2

I had later figured out the issue. I had the changed the configFormatter in my WebAPIconfig.cs file:

  config.Formatters.Remove(config.Formatters.XmlFormatter);

Once I removed it, the LINQ query worked as expected. I hope it may help someone else in future!

sandiejat
  • 2,552
  • 19
  • 24