Web Api returns same data multiple times

Question

public class VillageDto
{
    public int id { get; set; }
    public string Name { get; set; }
    public string HindiName { get; set; }        
    public int CentreId { get; set; }
}

public class CentreDto
{
    public int id { get; set; }
    public string Name { get; set; }
    public string HindiName { get; set; }
}

public class BankDto
{
    public int id { get; set; }
    public string BankName { get; set; }
    public String BankShortName { get; set; }
}

public class GrowerDto
{
    public int Id { get; set; }        
    public int VillageId { get; set; }
    public VillageDto village { get; set; }
    public string Name { get; set; }
    public string FatherName { get; set; }
    public string HindiName { get; set; }
    public string HindiFatherName { get; set; }
    public string AccountNo { get; set; }
    public int CentreId { get; set; }
    public CentreDto centre { get; set; }
    public int BankId { get; set; }
    public BankDto bank { get; set; }
}

public MappingProfile()
{   
    Mapper.CreateMap<Village, VillageDto>();
    Mapper.CreateMap<VillageDto, Village>();
    Mapper.CreateMap<Grower, GrowerDto>();
    Mapper.CreateMap<GrowerDto, Grower>();
    Mapper.CreateMap<Bank, BankDto>();
    Mapper.CreateMap<BankDto, Bank>();  
}

public class GrowerController : ApiController
{
    private ApplicationDbContext _context;

    public GrowerController()
    {
        _context = new ApplicationDbContext();
    }

    public IHttpActionResult GetAllGrowers(int? pagenumber, int? pagesize)
    {
        var allgrowers = _context.growers.Include(v => v.village).ToList().Select(Mapper.Map<Grower, GrowerDto>).OrderBy(g => g.Id);
        var currentPageNumber = pagenumber ?? 1;
        var currentPageSize = pagesize ?? 5;
        return Ok(allgrowers.Skip((currentPageNumber - 1) * currentPageSize).Take(currentPageSize));       
    }
}

return results

1.primary key for grower is (id,villageid)
2.villageid is also foreign key and also primary key for village model
3.when api called then api returns the same data multiple times.

Could you please share the entity configuration code for `Grower` and `Village`? — atiyar, Feb 28 '21 at 05:00

score 0 · Answer 1 · answered Feb 28 '21 at 03:46

Firstly, don't do this:

var allgrowers = _context.growers.Include(v => v.village).ToList().Select(Mapper.Map<Grower, GrowerDto>).OrderBy(g => g.Id);

Instead, use Automapper's ProjectTo method to work with IQueryable. The above code will select ALL data and map everything to DTOs before paginating. You want to keep the IQuertable right to the end to ensure that it builds a query to just return the desired page of data.

Next, I would avoid initializing a module level variable for the DbContext in the constructor. You want to ensure that the DbContext is disposed when the controller is finished with. Normally when you are using Dependency Injection you would pass in a DbContext instance via the constructor. Since you are not doing that yet I would remove:

public GrowerController()
{
    _context = new ApplicationDbContext();
}

and instead scope an ApplicationDbContext within a using block where needed.

// For example purposes, you need to manage the mapper configurations rather 
// than rely on the static Mapper API. 
var config = new MapperConfiguration(cfg => {
    cfg.CreateMap<Village, VillageDto>();
    cfg.CreateMap<Grower, GrowerDto>();
    cfg.CreateMap<Bank, BankDto>();
});

using (var context = new ApplicationDbContext())
{
    var query = _context.growers
        .OrderBy(g => g.Id)
        .ProjectTo<GrowerDto>(config);

    var currentPageNumber = pagenumber ?? 1;
    var currentPageSize = pagesize ?? 5;
    var results = query
        .Skip((currentPageNumber - 1) * currentPageSize)
        .Take(currentPageSize)
        .ToList();

    return Ok(results);
}

From that you can inspect the results value using a breakpoint to see whether you are getting the data you expect. If you are seeing duplicates then I would look at running a profiler against your database to see what SQL is being generated, which you can run manually to inspect the results. I don't know off-hand what specifically might cause the same record to be repeated. It may be something suspect with your entity mappings or your particular schema state. (FKs)

Web Api returns same data multiple times

1 Answers1