none
Why my DbContext.Select() returns null when populating a different object? RRS feed

  • Question

  • Hi,

    Inquiry

    First, this code works fine:

    [HttpGet]
    public async Task<ActionResult<IEnumerable<Employee>>> GetEmployees()
    {
         return await _context.Employees.ToListAsync();
    }              

    But when I modify to the following code, it returns null:

    [HttpGet]
    public async Task<ActionResult<IEnumerable<EmployeeListItem>>> GetEmployees()
    {
         return await _context.Employees
              .Select(p => new EmployeeListItem
              {
                   Id = p.Id,
                   Name = (p.FirstName + " " + p.LastName)
              }).ToListAsync();
    }      

    My intent is to return a view model EmployeeListItem (2nd code) instead of the data model Employee (1st code).

    My view model EmployeeListItem contains Id, Name, and other properties I need for showing an employee list, but the data model Employee contains all fields pertaining to an employee record.


    Background:

    1. I am beginner and trying to learn C# EF Core with LINQ

    2. The above code is used in Web Api

    3. Currently using Visual Studio Community 16.3.1

    Thank you very much for your assistance!




    • Moved by CoolDadTx Monday, September 30, 2019 2:42 PM EF related
    Sunday, September 29, 2019 5:27 AM

All replies

  • What you are doing looks perfectly correct. If your first query returns N rows, then the second one should also return N rows. If it doesn't, there must be something strange going on which is not obvious from looking at the code.

    For example, it could fail it the "Select" method is not the LINQ extender but rather a different Select from another class in your project that happens to be in context and returns null. I am not claiming that this is precisely what is happening in your case, I'm just mentioning it as an example of something that could happen but we wouldn't be able to discover it by just looking at the code fragment in your post.

    Try using F12 on Select for "go to definition" to see where the method is defined. Also, try to step through the code using the debugger to see what it's doing. One good trick is to add a breakpoint in the constructor for EmployeeListItem. In this way, you can see if it is being called.

    • Proposed as answer by Cherkaoui.Mouad Sunday, September 29, 2019 10:33 AM
    • Unproposed as answer by Cherkaoui.Mouad Sunday, September 29, 2019 10:35 AM
    Sunday, September 29, 2019 6:56 AM
  • 1) ViewModels  do not travel between the WebAPI service and the MVC progam the WebAPI clinet. The viewmodel travels betwwen the view and the MVC controller.

    2) DTO or DTO(s), their job is to travel, travel between the WebAPI service and the WebAPI client.

    https://en.wikipedia.org/wiki/Data_transfer_object

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    3) EF Entities do not travel between the WebAPI service and client.

    https://juristr.com/blog/2012/10/lessions-learned-dont-expose-ef-entities-to-the-client-directly/

    You should learn to create thin controllers whether or not it is concerning an ASP.NET MVC or WebAPI controller, and IMO the WebAPI controller should never be doing direct DB access, which can be done  implementing SoC either by shifting data access to objects in the Models folder or using a Data Access Layer aka DAL.

    using System.Collections.Generic;
    using System.Threading.Tasks;
    using DAL;
    using Entities;
    using Microsoft.AspNetCore.Mvc;
    
    namespace WebAPI.Controllers
    {
        [Produces("application/json")]
        [Route("api/[controller]")]
        [ApiController]
        public class ArticleController : ControllerBase
        {
            private IDaoArticle dao;
            public ArticleController(IDaoArticle daoArticle)
            {
                dao = daoArticle;
            }
    
            [HttpGet]
            [Route("GetAll")]
            public async Task<List<DtoArticle>> GetAll()
            {
                return await dao.GetAll();
            }
    
            [HttpGet]
            [Route("GetArticlesByAuthorId")]
            public async Task<List<DtoArticle>> GetArticlesByAuthorId(int id)
            {
                return await dao.GetArticlesByAuthorId(id);
            }
    
    
            [HttpGet]
            [Route("Find")]
            public async Task<DtoArticle> Find(int id)
            {
                return await dao.Find(id);
            }
    
            [HttpPost]
            [Route("Add")]
            public async Task Add(DtoArticle dto)
            {
                await dao.Add(dto);
            }
    
            [HttpPost]
            [Route("Update")]
            public async Task Update(DtoArticle dto)
            {
                await dao.Update(dto);
            }
    
            [HttpPost]
            [Route("Delete")]
            public async Task Delete(DtoId dto)
            {
                await dao.Delete(dto.Id);
            }
        }
    }

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using DAL.Models;
    using Entities;
    using Microsoft.EntityFrameworkCore;
    
    namespace DAL
    {
        public class DaoArticle :IDaoArticle
        {
            private PublishingCompanyContext pc;
            private IDaoAuthor _daoAuthor;
    
            public DaoArticle(PublishingCompanyContext dbcontext, IDaoAuthor daoAuthor)
            { 
                pc = dbcontext;
                _daoAuthor = daoAuthor;
            }
            public async Task<List<DtoArticle>> GetAll()
            {
                var dtos = new List<DtoArticle>();
    
                var articles = await pc.Article.ToListAsync();
    
                foreach (var article in articles)
                {
                    var dto = new DtoArticle
                    {
                        ArticleId = article.ArticleId,
                        AuthorId = article.AuthorId,
                        Title = article.Title,
                        Body = article.Body
                    };
    
                    dtos.Add(dto);
                }
    
                return dtos;
            }
    
            public async Task<List<DtoArticle>> GetArticlesByAuthorId(int id)
            {
                var dtos = new List<DtoArticle>();
    
                var articles = await pc.Article.Where(a => a.AuthorId.ToString().Contains(id.ToString())).ToListAsync();
               
                foreach (var article in articles)
                {
                    var intid = (int)article.AuthorId;
    
                    var dtoauthor = await _daoAuthor.Find(intid);
    
                    var dto = new DtoArticle
                    {
                        ArticleId = article.ArticleId,
                        AuthorId = article.AuthorId,
                        AuthorName = dtoauthor.LastName +", " + dtoauthor.FirstName,
                        Title = article.Title,
                        Body = article.Body
                    };
    
                    dtos.Add(dto);
                }
                 
                return dtos;
            }
            public async Task<DtoArticle> Find(int id)
            {
                var dto = new DtoArticle();
    
                var article = await pc.Article.FindAsync(id);
                
                if (article != null)
                {
                    dto.ArticleId = article.ArticleId;
                    dto.AuthorId = article.AuthorId;
                    dto.Title = article.Title;
                    dto.Body = article.Body;
                }
                else
                {
                    throw new Exception($"Article with ID = {id} was not found.");
                }
    
                return dto;
    
            }
    
            public async Task Add(DtoArticle dto)
            {
                var article = new Article
                {
                    AuthorId = dto.AuthorId,
                    Title = dto.Title,
                    Body = dto.Body
                };
    
                pc.Article.Add(article);
                await pc.SaveChangesAsync();
    
            }
    
            public async Task Update(DtoArticle dto)
            {
                var article = new Article
                {
                    ArticleId = dto.ArticleId,
                    AuthorId = dto.AuthorId,
                    Title = dto.Title,
                    Body = dto.Body
                };
    
                pc.Entry(article).State = EntityState.Modified;
                await pc.SaveChangesAsync();
    
            }
    
            public async Task Delete(int id)
            {
                var article = pc.Article.Find(id);
    
                if (article != null)
                {
                    pc.Article.Remove(article);
                    await pc.SaveChangesAsync();
                }
            }
    
        }
    }



    • Edited by DA924x Sunday, September 29, 2019 8:51 AM
    Sunday, September 29, 2019 8:36 AM
  • Hello,

    Have you tried using ConfigureAwait?


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, September 29, 2019 10:25 AM
  • Hi,

    I'll just add two links that give an idea about Karen's solution :

    ToListAsync() does not complete at all.

    the second link is about another kind of issues and thus when using Where since it returns an IQueryable :

    the source IQueryable doesn't implement IDbAsyncEnumerable

    Best regards,

    Mouad.



    Sunday, September 29, 2019 12:26 PM
  • Sir Alberto,

    Thank you very much for your reply, because of your observation, I rechecked my code, and removed some of the string functions that I used when building my Select() statement, and found out that string.First() is causing the problem.

    I originally used the following code:

    return await _context.Employees
              .Select(p => new EmployeeListItem
              {
                   Id = p.Id,
                   Name = (p.FirstName + " " + p.LastName),
                   Initials = String.Concat(p.FirstName.First(),p.MiddleName.First(),p.LastName.First())
              }).ToListAsync();

    I was stunned when you said the code I sent looks perfectly correct, so I removed the following code:

    Initials = String.Concat(p.FirstName.First(),p.MiddleName.First(),p.LastName.First())

    When I ran the code, it ran perfectly!

    Somehow the string.First() function causes the .Select() to return null. So I changed the code into:

    Initials = String.Concat(p.FirstName[0],p.MiddleName[0],p.LastName[0])

    Now the code works perfectly!

    Once again, thank you sir Alberto!

    Sunday, September 29, 2019 1:00 PM
  • Sir DA924x,

    Wow the code looks very readable, manageable, and very clean. I'd like to code like that.

    Thank you for the suggestions and the references sir, I will definitely study them.

    Sunday, September 29, 2019 1:02 PM
  • Hi Ma'am Karen and Sir Cherkaoui,

    Thanks for the suggestions, I will also study these concepts.

    Sunday, September 29, 2019 1:06 PM