CODEWITHSUNDEEP
c#entity-framework-6
Tanwer
Tanwer

Reputation: 1583

Entity Framework Returning Extra unwanted Data

I am using Entity Framework in my project. There are 3 tables in the .edmx model file:

Table Category master with these two columns:

* Category_id (int, primary key)
* Category_Name (nvarchar)

There are two other tables Sub_Category_Master and Question_master. Both have a FK Category_id which is linked to the PK of category_Master.

My problem is when I access the data 

dt = context.Category_Master.ToList().ToDataTable();

Than it should return data from Category_Master table only, but instead of this I am getting two additional columns Question_Master and Sub_Category_master with values of type System.Collection:

enter image description here

It is causing problem as this is an api and getting failed due to unwanted values

Although I have a workaround for this by using below approach 

dt = context.Category_Master.Select(x => new
            {
                Category_id = x.Category_id,
                Category_Name = x.Category_Name
            }).ToList().ToDataTable();

This returns only required values, but in case in future if we need to add additional columns in table than adjustment for new column will be required here in this case which I don't want to do, neither I want to use ado.net with select * from table statement. So how can I get only data for category master using Entity Framework like below snapshot

enter image description here

Update one

Below is the auto generated class of Category_master.cs

public partial class Category_Master
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Category_Master()
    {
        this.Question_Master = new HashSet<Question_Master>();
        this.Sub_Category_Master = new HashSet<Sub_Category_Master>();
    }

    public int Category_id { get; set; }
    public string Category_Name { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Question_Master> Question_Master { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Sub_Category_Master> Sub_Category_Master { get; set; }
}

If I comment out virtual ICollection of other classes like below, then will it affect somewhere in my code? Or table relationship?

 public partial class Category_Master
 {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Category_Master()
        {
            //this.Question_Master = new HashSet<Question_Master>();
            //this.Sub_Category_Master = new HashSet<Sub_Category_Master>();
        }

        public int Category_id { get; set; }
        public string Category_Name { get; set; }

        //[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        //public virtual ICollection<Question_Master> Question_Master { get; set; }
        //[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        //public virtual ICollection<Sub_Category_Master> Sub_Category_Master { get; set; }
}

Upvotes: 0

Views: 46

Answers (2)

koryakinp
koryakinp

Reputation: 4125

If you do not want to deal with AutoMapper and DTOs, you can use OData:

PM> Install-Package Microsoft.Data.OData

public class CategoryController : ODataController
{
    private readonly YourDbContext _ctx;
    public CategoryController(YourDbContext ctx)
    {
        _ctx = ctx;
    }        

    [EnableQuery]
    public IQueryable<Category_Master> Get()
    {
       return _ctx.Category_Master;
    }
}

Request to select Category_ID and Category_Name only:

GET /odata/Category?$select=Category_Name,Category_Id

Upvotes: 1

koryakinp
koryakinp

Reputation: 4125

Instead of returning an Entity, return DTO object:

public class CategoryDTO
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
}

You can use AutoMapper to map entity to DTO classes, something like that:

CreateMap<Category, CategoryDTO>()
    .ForMember(q => q.CategoryName, w => w.MapFrom(e => e.Category_Name))
    .ForMember(q => q.CategoryId, w => w.MapFrom(e => e.Category_Id));

By utilizing Queryable Extensions you can query the database in a following way:

context.Category_Master.ProjectToList<CategoryDTO>();

Upvotes: 1

Related Questions