How to select just some fields from a table in EF

Question

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.

Answer 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.

Answer 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.