Many to Many Query with Generic Repostitory

Question

Could someone please point me in the right direction of querying a many to many relationship. I'm trying to get a List of all active Machine Operators and for the life of me cannot seem to get it right.

If its of any use, this is what I've been trying up to now

public HttpResponseMessage Get()
    {
        using (DAL.Repositories.Repository<Machine> machineRepo = new DAL.Repositories.Repository<Machine>())
        using (DAL.Repositories.Repository<Operator> operatorRepo = new DAL.Repositories.Repository<Operator>())
        {
            List<int> OperatorIDs = new List<int>();
            List<Machine> Machine = new List<Machine>();

            OperatorIDs = operatorRepo.FindAll(x => x.Active).Select(y => y.ID).ToList();

            var machineOperators = machineRepo.FindAll(x => x.Active).Select(x => x.Operators.All(o => OperatorIDs.Contains(o.ID)));


            return Request.CreateResponse(HttpStatusCode.OK, machineOperators);
        }
    }

My code as follows

public class Operator
{
    public Operator()
    {
        Machines = new HashSet<Machine>();
    }

    public int ID { get; set; }
    public string FirstName { get; set; }
    public string Surname { get; set; }
    public bool Active { get; set; }

    // Navigational Link
    public virtual ICollection<Machine> Machines { get; set; }
}

public class Machine
{
    public Machine()
    {
        Departments = new HashSet<Department>();
        Operators = new HashSet<Operator>();
    }

    public int ID { get; set; }
    public string MachineName { get; set; }
    public bool Active { get; set; }

    public ICollection<Operator> Operators { get; set; }
}

And in my efContext

public MachineConfiguration()
    {
        // Key
        HasKey(x => x.ID);

        // Fields
        Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(x => x.MachineName).IsRequired();

        // Table
        ToTable("Machine");

        // Relationships
        HasMany(e => e.Operators)
            .WithMany(e => e.Machines)
            .Map(m => m.ToTable("MachineOperators")
                .MapLeftKey("MachineID")
                .MapRightKey("OperatorID"));
    }

What do you want to return? All the active machines that have all the operators in active? — ocuenca, Apr 09 '15 at 15:02
A list of Active Machines with their corresponding Active Operators — John Cooling, Apr 09 '15 at 15:05

ocuenca · Answer 1 · 2015-04-09T15:12:47.817

0

Try this:

public HttpResponseMessage Get()
{
    using (DAL.Repositories.Repository<Machine> machineRepo = new DAL.Repositories.Repository<Machine>())
    {
        var machineOperators = machineRepo.FindAll(x => x.Active).Include(x=>x.Operators).Where(x => x.Operators.All(o =>o.Active)).ToList();

        return Request.CreateResponse(HttpStatusCode.OK, machineOperators);
    }
}

edited Apr 09 '15 at 15:12

answered Apr 09 '15 at 15:06

ocuenca

38,548
11
89
102

Returns the active machines, but no operators `{ "$id": "1", "ID": 1, "MachineName": "Roland X1000", "Active": true, "Operators": [], "Departments": [] }` – John Cooling Apr 09 '15 at 15:09
Maybe you have disabled lazy loading, try eager loading that nav property using the `Include` extension method. – ocuenca Apr 09 '15 at 15:12
Now I saw your `Operator` nav property is not `virtual`, If you want that EF tracks the changes in your entities and lazy load your navigation properties, you need to declare them as virtual: `public virtual ICollection Operators { get; set; }` – ocuenca Apr 09 '15 at 15:21
Yes, that worked great. Now i'm faced with this error when the results are returned. `The 'ObjectContent1' type failed to serialize the response body for content type 'application/json; charset=utf-8` – John Cooling Apr 09 '15 at 15:39
Check the answers in these posts: [post1](https://social.msdn.microsoft.com/Forums/vstudio/en-US/a5adf07b-e622-4a12-872d-40c753417645/web-api-error-the-objectcontent1-type-failed-to-serialize-the-response-body-for-content), [post2](http://stackoverflow.com/questions/13959048/asp-net-web-api-error-the-objectcontent1-type-failed-to-serialize-the-respon) and [post3](http://stackoverflow.com/questions/12936713/web-api-error-the-objectcontent1-type-failed-to-serialize-the-response-body) – ocuenca Apr 09 '15 at 16:09
Lazy loading and serialization are not "_good friends_". Probably you may need to disable [proxy creation](https://msdn.microsoft.com/en-us/data/jj592886.aspx) but first try the solution that is recomended in the [post1](https://social.msdn.microsoft.com/Forums/vstudio/en-US/a5adf07b-e622-4a12-872d-40c753417645/web-api-error-the-objectcontent1-type-failed-to-serialize-the-response-body-for-content) – ocuenca Apr 09 '15 at 16:31
It would seem that code is already in place, disabling the ProxyCreation works but it creates a weird output in JSON but this also breaks other areas of my application. – John Cooling Apr 10 '15 at 09:26
well my friend, those are the solutions that you will find to resolve that problem. ;). I think you should create a new question to see if some user who have previously had the same issue can help you. – ocuenca Apr 10 '15 at 13:28

Many to Many Query with Generic Repostitory

1 Answers1