using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace EFCoreIssue
{
    class Program
    {
        static void Main(string[] args)
        {
            var context = new TestDbContext();

            var query = context.Offers.AsQueryable();

            query = query.Join(context.UserHierarchys.Select(h => h.CanAccessId).Distinct(), q => q.UserIdCreated, k => k, (q, h) => q);
            query = query.Include(p => p.Etikettes);

            var q = query.ToArray();
        }
    }

    public class TestDbContext : DbContext
    {
        public DbSet<UserHierarchy> UserHierarchys { get; set; }
        public DbSet<Offer> Offers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseSqlServer("server=.;database=myDb;trusted_connection=true;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<UserHierarchy>(uh =>
            {
                uh.HasKey(p => new
                {
                    p.Hierarchy,
                    p.Id,
                    p.CanAccessId
                });
            });

            modelBuilder.Entity<Offer>(o =>
            {
                o.HasMany(p => p.Etikettes).WithOne(p => p.Offer);
            });
        }
    }

    public class UserHierarchy
    {
        public string Hierarchy { get; set; }
        public Guid Id { get; set; }
        public Guid CanAccessId { get; set; }
        public string Privilege { get; set; }
    }

    public class Offer
    {
        public Guid Id { get; set; }
        public Guid? UserIdCreated { get; set; }


        public virtual ICollection<OfferEtikette> Etikettes { get; set; }
    }

    public class OfferEtikette
    {
        public Guid Id { get; set; }
        public Guid OfferId { get; set; }

        public virtual Offer Offer { get; set; }
    }
}