How to select just some fields from a table in EF

27,956

Solution 1

Assume you have a table with this model:

public class User{
    public int ID {get; set;}
    public string NickName {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public string FotherName {get; set;}
    public DateTime BirthDate {get; set;}
    public string Mobile {get; set;}
    public string Email {get; set;}
    public string Password {get; set;}
}

Now, you want fetch just ID, FirstName, LastName, and FotherName. You can do it in 2 way; The first way is fetch them as an anonymous object, look:

var user = entityContext.Users.Where(u => u.ID == id)
    .Select(u => new {
        ID = u.ID,
        FirstName = u.FirstName,
        LastName = u.LastName,
        FotherName = u.FotherName
    }).Single();

Now, your return-value-type is anonymous, you can work with it such as:

var i = user.ID;
// or
var s = user.FirstName;

In another way (for example when you want to pass the object as an Model to a View), you can define a new class, (i.e. UserViewModel), and when you select the object, select it as a UserViewModel. look:

public class UserViewModel{
    public int ID {get; set;}
    public string NickName {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public string FotherName {get; set;}
}

and in query, take this:

var user = entityContext.Users.Where(u => u.ID == id)
    .Select(u => new UserViewModel {
        ID = u.ID,
        FirstName = u.FirstName,
        LastName = u.LastName,
        FotherName = u.FotherName
    }).Single();

Look that just ONE difference is between them, in labda expression, instead of u => new {} we are using u => new UserViewModel{}. Good luck.

Solution 2

There can be many ways to do this job, but using Automapper NuGet package is the most simple one I have experienced.

  • First: Install Autmapper NuGet package for your project from NuGet package explorer.
  • Second: Make a simple ViewModel, which contains only required attributes:

    public class UserViewModel {
        public int ID {get; set;}
        public string FirstName {get; set;}
        public string LastName {get; set;}
    }
    
  • Third: Initialize your your mapper only for once in app_start class like:

    namespace SampleProject.App_Start {
        public class AutoMapperConfig {
            public static void Initializer() {
                AutoMapper.Mapper.Initialize(cfg => {
    
                    cfg.CreateMap<User, UserViewModel>()
                });
             }
         }
    }
    
  • Fourth: Add it's entry in Global.asax.cs:

    namespace SampleProject
    {
        public class MvcApplication : System.Web.HttpApplication
        {
            protected void Application_Start()
            {
                AreaRegistration.RegisterAllAreas();
                GlobalConfiguration.Configure(WebApiConfig.Register);
                FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
                RouteConfig.RegisterRoutes(RouteTable.Routes);
                BundleConfig.RegisterBundles(BundleTable.Bundles);
    
                // AutoMapper Initializer
                App_Start.AutoMapperConfig.Initializer();
            }
        }
    }
    
  • Fifth: Use it in your controller where you want like this:

    namespace SampleProject.Controllers
    {
        public class UsersController : Controller
        {
            private DataContext db = new DataContext();
    
            public ActionResult Index()(
                var model = AutoMapper.Mapper.Map<List<UserViewModel>>(db.User.ToList());
                return View(model);
    
            }
        }
    }
    
  • Last: You can create as many maps as you want between your Models and ViewModels by initializing them once in the app_start's AutoMapperConfig class and use them where you want with a simple line of code.

Also you can find a lot of help about Automapper if you search about it. Its main website is here.




Important:

I am a developer of ASP.NET MVC 5. Automapper works fine for me every time. I cannot check it on MVC 3 or older than MVC 5.

Share:
27,956
Bevan
Author by

Bevan

Professional software developer in Wellington, New Zealand.

Updated on July 09, 2022

Comments

  • Bevan
    Bevan over 1 year

    I have a table with 9 columns in database and I want to be able to load only some fields of it if I need.

    How can I do this with Entity Framework 4 please?

    e.g. My table has these fields:

    ID, FirstName, LastName, FotherName, BirthDate, Mobile, Email
    

    and I want to be able to fetch just these columns:

    ID, FirstName, LastName
    

    My project is an ASP.NET MVC 3 application, with SQLServer 2008 Express and EF 4.1.

  • Worthy7
    Worthy7 over 7 years
    Why are we not just using constructors in viewmodels?
  • SAR
    SAR over 6 years
    @Java_Amiry how to return list not single
  • amiry jd
    amiry jd over 6 years
    @SAR just replace the .Single() method with .ToList() one
  • SAR
    SAR over 6 years
    @Javad_Amiry i had tried it should have work but not in my case
  • farshad
    farshad over 5 years
    @javadamiry Thanks, .ToList() works fine. But when using the code above with .ToList() doesn't it load the whole table into application and then only keep the desired column? What if I want to load only the desired column and use it? (Because loading whole table takes much more resources.)
  • amiry jd
    amiry jd over 5 years
    @farshad nope. that's a common misunderstanding in most coders. the 'ToList' makes the ef to build-up the final query and send it to db, and just loads what did you asked in query. don't worry. use it.