How to select just some fields from a table in EF
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
andViewModels
by initializing them once in theapp_start
'sAutoMapperConfig
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
.
Bevan
Professional software developer in Wellington, New Zealand.
Updated on July 09, 2022Comments
-
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, withSQLServer 2008 Express
andEF 4.1
. -
Worthy7 over 7 yearsWhy are we not just using constructors in viewmodels?
-
SAR over 6 years@Java_Amiry how to return list not single
-
amiry jd over 6 years@SAR just replace the
.Single()
method with.ToList()
one -
SAR over 6 years@Javad_Amiry i had tried it should have work but not in my case
-
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 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.