C# .NET: Entity Framework Examples

12/5/2013 6:42:08 PM

The following are EF (Entity Framework) examples for pulling data from a database (or any source).

  • Get - Get and object by a unique Id.
  • List - List all objects in the source.
  • List By Value - List by a value in the object.
  • List By Related Table Value - List using a join and a value in the joined table.
  • List By Page - List from a table using database pagination.
  • Count - Count of all records in the source.
  • Count By Value = Count of all records in a source by with a certain value.

*Not sure if it matters but using .NET 4.5 and EF 5.0 and code first design.

//DB Context Create Separately
public DbSet<MyObject> MyObjects { get; set; }
public DbSet<Resource> Resources { get; set; }
public DbSet<User> Users { get; set; }

....

//the object "db" represents an initialized DbContext, initialization not shown

//Get
public MyObject Get(int id)
{	
	if (id > 0)
	{
		return db.MyObjects.FirstOrDefault(x => x.Id == id);
	}

	return null;
}
//Get
public MyObject Get(int id)
{	
	if (id > 0)
	{
		var query = from myObjects in db.MyObjects
					where myObjects.Id == id
					select myObjects;

				return query.FirstOrDefault();
	}

	return null;
}

//List
public List<MyObject> List()
{
	return db.MyObjects.ToList();
}
//List
public List<MyObject> List()
{
	var query = from myObjects in db.MyObjects
				select myObjects;
				
	return query.ToList();
}


//List By Value
public List<MyObject> List_By_StatusId(int statusId)
{
	return db.MyObjects.Where(x => x.StatusId == statusId).ToList();
}
//List By Value
public List<MyObject> List_By_StatusId(int statusId)
{
	var query = from myObjects in db.MyObjects
			    where myObjects.StatusId == statusId
			    select myObjects;

	return list.ToList();
}


//List By Related Table Value
public List<Resource> List_By_Username(string username)
{
	if (!string.IsNullOrEmpty(username))
	{
		var query = from resources in db.Resources
					join users in db.Users on resources.UserId equals users.Id

					where users.Username == username

					select resources;

		return query.ToList();
	}

	return new List<Entities.Resource>();
}


//List By Page
public List<Resource> List_By_Page(int page, int recordsPerPage)
{			
	if (page <= 0)
	{
		page = 1;
	}
	int recordToSkip = (page - 1) * recordsPerPage;

	var query = from resources in db.Resources
				orderby resources.Id descending
				select resources;

	return query.Skip(recordToSkip).Take(recordsPerPage).ToList();
}


//Count
public int Count()
{
	return db.Resources.Count();
}
//Count
public int Count()
{
	var query = from resources in db.Resources
				select resources;

	return query.Count();
}


//Count By Value
public int Count_By_Value(int userId)
{
	if (userId > 0)
	{
		return db.Resources.Count(x => x.UserId == userId);
	}

	return 0;
}
//Count By Value
public int Count_By_Value(int userId)
{
	if (userId > 0)
	{
		var query = from resources in db.Resources
					where resources.UserId == userId
					select resources;

		return query.Count();
	}

	return 0;
}