Is this the correct way I can get linked tables using where or is there another way to return the data.

GetMethod

public IEnumerable<Model.MeetingPollingQuestion> GetMeetingPollingQuestion(int MeetingPollingId)
        {
            using (var db = new NccnEcommerceEntities())
            {
                using (DbContextTransaction dbTran = db.Database.BeginTransaction())
                {
                    var ListOfMeetingPollingQuestions = (from mpq in db.MeetingPollingQuestions
                                                         where (mpq.MeetingPollingId == MeetingPollingId)
                                                         select new Model.MeetingPollingQuestion
                                                         {
                                                             MeetingPollingId = mpq.MeetingPollingId.Value,
                                                             MeetingPollingQuestionType = mpq.MeetingPollingQuestionType,
                                                             MeetingPollingParts = (from mp in db.MeetingPollingParts
                                                                                    where mp.MeetingPollingPartsId == mpq.MeetingPollingId
                                                                                    select new Model.MeetingPollingParts
                                                                                      {
                                                                                          Type= mp.Type
                                                                                      }).ToList(),
                                                         }).ToList();
                    return ListOfMeetingPollingQuestions;
                }
            }
        }

EF Model

    namespace Repository.EFModel
    {
        using System;
        using System.Collections.Generic;
        
        public partial class MeetingPolling
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public MeetingPolling()
            {
                this.MeetingPollingQuestions = new HashSet<MeetingPollingQuestion>();
            }
        
            public int MeetingPollingId { get; set; }
            public Nullable<int> MeetingId { get; set; }
            public Nullable<System.DateTime> StartDate { get; set; }
            public Nullable<System.DateTime> EndDate { get; set; }
            public string PollingTitle { get; set; }
        
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<MeetingPollingQuestion> MeetingPollingQuestions { get; set; }
        }
    }


namespace Repository.EFModel
{
    using System;
    using System.Collections.Generic;
    
    public partial class MeetingPollingQuestion
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public MeetingPollingQuestion()
        {
            this.MeetingPollingParts = new HashSet<MeetingPollingPart>();
        }
    
        public int MeetingPollingQuestionId { get; set; }
        public string MeetingPollingQuestionType { get; set; }
        public Nullable<int> MeetingPollingId { get; set; }
        public Nullable<int> SequenceOrder { get; set; }
    
        public virtual MeetingPolling MeetingPolling { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<MeetingPollingPart> MeetingPollingParts { get; set; }
    }
}


namespace Repository.EFModel
{
    using System;
    using System.Collections.Generic;
    
    public partial class MeetingPollingPart
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public MeetingPollingPart()
        {
            this.MeetingPollingPartsValues = new HashSet<MeetingPollingPartsValue>();
        }
    
        public int MeetingPollingPartsId { get; set; }
        public string Type { get; set; }
        public Nullable<int> MeetingPollingQuestionId { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<MeetingPollingPartsValue> MeetingPollingPartsValues { get; set; }
        public virtual MeetingPollingQuestion MeetingPollingQuestion { get; set; }
    }
}

CodePudding user response：

Your entities have navigation properties for the related entities, so if you want to return entities and their relatives, just eager load the related entities using Include.

public IEnumerable<Model.MeetingPollingQuestion> GetMeetingPollingQuestion(int MeetingPollingId)
{
    using (var db = new NccnEcommerceEntities())
    {
        var questions = db.MeetingPollingQuestions
            .AsNoTracking()   
            .Include(mpq => mpq.MeetingPollingType)
            .Include(mpq => mpq.MeetingPollingParts)
            .Where(mpq => mpq.MeetingPollingId == MeetingPollingId)
            .ToList();
        return questions;
    }
}

... and that's it. The important details here is the use of Include to eager load related data, and the use of AsNoTracking to ensure that the loaded entities are not tracked by the DbContext. These will be detached entities, copies of the data but otherwise not tracking changes or an association with a DbContext. These are suitable for read-only access to the data they contain.

Whenever returning "entities" outside of the scope of a DbContext you should ensure that they are non-tracked or detached. This is to avoid errors that can come up with potential lazy load scenarios complaining about that an associated DbContext has been disposed, or errors about references being tracked by another DbContext if you try associating these entities to a new DbContext. Your code performing a Select with a new class instance does the same thing, just more code.

Personally I do not recommend working with detached entities such as ever returning entities outside of the scope of the DbContext that they were read from. Especially if you decide you only need a subset of data that the entity ultimately can provide. Entities reflect the data state, and should always be considered as Complete, or Complete-able. (i.e. Lazy loading enabled) If the code base has some code that works with tracked entities within the scope of a DbContext, vs. detached entities, vs. copies of entity classes that might be partially filled or deserialized, it makes for buggy, unreliable, and un-reusable code. Take a utility method that accepts a MeetingPollingQuestion as a parameter. As far as that method is concerned it should always get a complete MeetingPollingQuestion. The behaviour of this method could change depending on whether it was given a tracked vs. detached, vs. partially filled in copy of a MeetingPollingQuestion class. Methods like this would need to inspect the entity being passed in, and how reliable can that logic be for determining why a related entity/collection might be missing or #null?

If you need to pass entity data outside the scope of the DbContext where you cannot count on that data being complete or related data being lazy loaded as a last resort, then I recommend using POCO DTOs or ViewModels for those detached or partial representations of the data state. With a separate POCO (Populated by Select or Automapper) there is no confusion between that representation and a tracked Entity.