none
LINQ To Entity Result Formatting With Three Models RRS feed

  • Question

  • I have three models.  There is a one-to-many model that joins to a many-to-one model with one connecting model in between.  I can't quite get the LINQ query formatted right perhaps?

    I can pull the data in long rows but will need it to be formatted in more of a pivot table model:

    This:

    [
      {
        "ROLE_ID":1132,
    	"LOCK_ID":443,
    	"ROLE_NAME":"LMG2",
    	"ROLE_DESC":"LONG_TERM_MGR",
        "SUBS": [
          {
            "JOB_ID":62,
    		"JOB_TYPE":"MGR",
    		"JOB_VALUE":"MGR2",
    		"JOB_NAME":"RED"
          },
    	  {
    	  "JOB_ID":63,
    	  "JOB_TYPE":"MGR",
    	  "JOB_VALUE":"MGR3",
    	  "JOB_NAME":"BLACK"
    	  }
        ]
      }
    ]



    Instead of this:

    [{
    "ROLE_ID":1132,
    "LOCK_ID":443,
    "ROLE_NAME":"LMG2",
    "ROLE_DESC":"LONG_TERM_MGR",
    "JOB_ID":62,
    "JOB_TYPE":"MGR",
    "JOB_VALUE":"MGR2",
    "JOB_NAME":"RED"
    },
    {
    "ROLE_ID":1132,
    "LOCK_ID":443,
    "ROLE_NAME":"LMG2",
    "ROLE_DESC":"LONG_TERM_MGR",
    "JOB_ID":63,
    "JOB_TYPE":"MGR",
    "JOB_VALUE":"MGR3",
    "JOB_NAME":"BLACK"
    }]
    


    MODELS:

    //Model that is one-to-many
    public class MAP_ACCESS_ROLES
    {
    	[Key]
    	public int ROLE_ID { get; set; }
    	public int LOCK_ID { get; set; }
    	public string ROLE_NAME { get; set; }
    	public string ROLE_DESC { get; set; }
    	public ICollection<MAP_FGA_UNITS> SUBS { get; set; }
    }
    
    //Joining model
    public partial class ROLE_FINE_GRAIN_ACCESS_UNITS
    {
    	[Column(Order = 0)]
    	public int ROLE_ID { get; set; }
    
    	[Column(Order = 1)]
    	public int JOB_ID { get; set; }
    }
    
    //Model that is many to one
    public class MAP_JOB_UNITS
    {
    	[Key]
    	public int JOB_ID { get; set; }
    	public string JOB_TYPE { get; set; }
    	public string JOB_NAME { get; set; }
    	public string JOB_VALUE { get; set; }
    }

    ATTEMPTED PARTIAL QUERY:

    var qry2 = db.ACCESS_ROLES
    .Join(db.ROLE_FINE_GRAIN_ACCESS_UNITS, a => a.ROLE_ID, b => b.ROLE_ID, (a, b) => new {a, b})
    .Join(db.FINE_GRAIN_ACCESS_UNITS, c => c.b.JOB_ID, d => d.JOB_ID, (c, d) => new {c, d})
    .Select(f => new
    {
    	ROLE_ID = f.c.b.ROLE_ID,
    	ROLE_NAME = f.c.a.ROLE_NAME,
    	ROLE_DESC = f.c.a.ROLE_DESC,
    	LOCK_ID = f.c.a.CDP_ID,
    	JOB_ID = f.d.JOB_ID,
    	JOB_NAME = f.d.JOB_NAME
    }); 


    Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D

    Thursday, June 15, 2017 1:30 PM

Answers

  • Hi polymorphic,

    Please check the following code.

    #models

     public class MAP_ACCESS_ROLES
        {
            [Key]
            public int ROLE_ID { get; set; }
            public int LOCK_ID { get; set; }
            public string ROLE_NAME { get; set; }
            public string ROLE_DESC { get; set; }
            public virtual ICollection<ROLE_FINE_GRAIN_ACCESS_UNITS> SUBS { get; set; }
        }
    
        //Joining model
        public partial class ROLE_FINE_GRAIN_ACCESS_UNITS
        {
            [Key]
            [Column(Order = 0)]
            public int ROLE_ID { get; set; }
    
            [Key]
            [Column(Order = 1)]
            public int JOB_ID { get; set; }
        }
    
        //Model that is many to one
        public class MAP_JOB_UNITS
        {
            [Key]
            public int JOB_ID { get; set; }
            public string JOB_TYPE { get; set; }
            public string JOB_NAME { get; set; }
            public string JOB_VALUE { get; set; }
        }

    #DbContext

    public partial class EfDemoContext : DbContext
        {
            public EfDemoContext()
                : base("name=EfDemoContext")
            {
            }
    
            public DbSet<MAP_ACCESS_ROLES> ACCESS_ROLES { get; set; }
            public DbSet<ROLE_FINE_GRAIN_ACCESS_UNITS> ROLE_FINE_GRAIN_ACCESS_UNITS { get; set; }
            public DbSet<MAP_JOB_UNITS> MAP_JOB_UNITS { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
            }
        }

    #Usage:

    static void Main(string[] args)
            {
                using (var db = new EfDemoContext())
                {
                    var result = db.ACCESS_ROLES.Select(t => new {
                        ROLE_ID = t.ROLE_ID,
                        LOCK_ID = t.LOCK_ID,
                        ROLE_NAME = t.ROLE_NAME,
                        ROLE_DESC = t.ROLE_DESC,
                        SUBS = db.MAP_JOB_UNITS.Where(x => t.SUBS.Select(a => a.JOB_ID).ToList().Contains(x.JOB_ID)).ToList()
                    }).ToList();
    
                    string jsonString = JsonConvert.SerializeObject(result);
    
                    Console.WriteLine(jsonString);
                    Console.ReadKey();
                }
            }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by polymorphic Friday, June 16, 2017 5:41 PM
    Friday, June 16, 2017 9:51 AM
    Moderator

All replies

  • This is basically because you haven't declared Navigation Properties on your entities to enable EF to return shaped data.

    Any time you are using Join in EF, it's probably because your model is missing Navigation Properties.

    EG

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp1
    {
    
        //Model that is one-to-many
        public class MAP_ACCESS_ROLES
        {
            [Key]
            public int ROLE_ID { get; set; }
            public int LOCK_ID { get; set; }
            public string ROLE_NAME { get; set; }
            public string ROLE_DESC { get; set; }
    
            public virtual ICollection<ROLE_FINE_GRAIN_ACCESS_UNITS> ROLE_FINE_GRAIN_ACCESS_UNITS  { get;} = new HashSet<ROLE_FINE_GRAIN_ACCESS_UNITS>();
           // public ICollection<MAP_FGA_UNITS> SUBS { get; set; }
        }
    
        //Joining model
        public partial class ROLE_FINE_GRAIN_ACCESS_UNITS
        {
            [Key]
            [Column(Order = 0)]
            public int ROLE_ID { get; set; }
    
            [Key]
            [Column(Order = 1)]
            public int JOB_ID { get; set; }
    
            [ForeignKey("JOB_ID")]
            public virtual MAP_JOB_UNITS MAP_JOB_UNIT { get;set;}
    
            [ForeignKey("JOB_ID")]
            public virtual MAP_ACCESS_ROLES MAP_ACCESS_ROLE { get; set; }
    
        }
    
        //Model that is many to one
        public class MAP_JOB_UNITS
        {
            [Key]
            public int JOB_ID { get; set; }
            public string JOB_TYPE { get; set; }
            public string JOB_NAME { get; set; }
            public string JOB_VALUE { get; set; }
    
            public virtual ICollection<ROLE_FINE_GRAIN_ACCESS_UNITS> ROLE_FINE_GRAIN_ACCESS_UNITS { get; } = new HashSet<ROLE_FINE_GRAIN_ACCESS_UNITS>();
        }
        public class Db : DbContext
    
        {
            public DbSet<MAP_ACCESS_ROLES> ACCESS_ROLES { get; set; }
            public DbSet<ROLE_FINE_GRAIN_ACCESS_UNITS> ROLE_FINE_GRAIN_ACCESS_UNITS { get; set; }
            public DbSet<MAP_JOB_UNITS> MAP_JOB_UNITS { get; set; }
    
    
        }
        class Program
        {
            static void Main(string[] args)
            {
                Database.SetInitializer(new DropCreateDatabaseAlways<Db>());
                using (var db = new Db())
                {
                    var q = from r in db.ACCESS_ROLES.Include("ROLE_FINE_GRAIN_ACCESS_UNITS.MAP_JOB_UNIT")
                            select r;
    
                    var sql = q.ToString();
                    
    
                    Console.ReadKey();
                }
            }
        }
    }
    

    David


    Microsoft Technology Center - Dallas
    My blog

    Thursday, June 15, 2017 2:20 PM
  • Thanks, I'm using Code-First Existing Database.  I do need to find a way to use navigation properties better.  I avoid them due to circular data issues.  When I test the above with generated controller code, I get the following:

    [
      {
        "ROLE_ID": 0,
        "LOCK_ID": 0,
        "ROLE_NAME": "string",
        "ROLE_DESC": "string",
        "ROLE_FINE_GRAIN_ACCESS_UNITS": [
          {
            "ROLE_ID": 0,
            "JOB_ID": 0,
            "FINE_GRAIN_ACCESS_UNITS": {
              "JOB_ID": 0,
              "JOB_TYPE": "string",
              "JOB_NAME": "string",
              "JOB_VALUE": "string",
              "ROLE_FINE_GRAIN_ACCESS_UNITS": [
                {}
              ]
            },
            "ACCESS_ROLES": {
              "ROLE_ID": 0,
              "LOCK_ID": 0,
              "ROLE_NAME": "string",
              "ROLE_DESC": "string",
              "SUBS": [
                {
                  "JOB_ID": 0,
                  "JOB_TYPE": "string",
                  "JOB_NAME": "string",
                  "JOB_VALUE": "string"
                }
              ]
            }
          }
        ]
      }
    ]

    I'll keep trying.


    Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D

    Thursday, June 15, 2017 6:22 PM
  • Hi polymorphic,

    Please check the following code.

    #models

     public class MAP_ACCESS_ROLES
        {
            [Key]
            public int ROLE_ID { get; set; }
            public int LOCK_ID { get; set; }
            public string ROLE_NAME { get; set; }
            public string ROLE_DESC { get; set; }
            public virtual ICollection<ROLE_FINE_GRAIN_ACCESS_UNITS> SUBS { get; set; }
        }
    
        //Joining model
        public partial class ROLE_FINE_GRAIN_ACCESS_UNITS
        {
            [Key]
            [Column(Order = 0)]
            public int ROLE_ID { get; set; }
    
            [Key]
            [Column(Order = 1)]
            public int JOB_ID { get; set; }
        }
    
        //Model that is many to one
        public class MAP_JOB_UNITS
        {
            [Key]
            public int JOB_ID { get; set; }
            public string JOB_TYPE { get; set; }
            public string JOB_NAME { get; set; }
            public string JOB_VALUE { get; set; }
        }

    #DbContext

    public partial class EfDemoContext : DbContext
        {
            public EfDemoContext()
                : base("name=EfDemoContext")
            {
            }
    
            public DbSet<MAP_ACCESS_ROLES> ACCESS_ROLES { get; set; }
            public DbSet<ROLE_FINE_GRAIN_ACCESS_UNITS> ROLE_FINE_GRAIN_ACCESS_UNITS { get; set; }
            public DbSet<MAP_JOB_UNITS> MAP_JOB_UNITS { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
            }
        }

    #Usage:

    static void Main(string[] args)
            {
                using (var db = new EfDemoContext())
                {
                    var result = db.ACCESS_ROLES.Select(t => new {
                        ROLE_ID = t.ROLE_ID,
                        LOCK_ID = t.LOCK_ID,
                        ROLE_NAME = t.ROLE_NAME,
                        ROLE_DESC = t.ROLE_DESC,
                        SUBS = db.MAP_JOB_UNITS.Where(x => t.SUBS.Select(a => a.JOB_ID).ToList().Contains(x.JOB_ID)).ToList()
                    }).ToList();
    
                    string jsonString = JsonConvert.SerializeObject(result);
    
                    Console.WriteLine(jsonString);
                    Console.ReadKey();
                }
            }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by polymorphic Friday, June 16, 2017 5:41 PM
    Friday, June 16, 2017 9:51 AM
    Moderator