The Daily Parker

Politics, Weather, Photography, and the Dog

Q is for Querying

Blogging A to ZPosting day 17 of the Blogging A-to-Z challenge just a little late because of stuff (see next post). Apologies.

Today's topic is querying, which .NET makes relatively easy through the magic of LINQ. Last week I showed how LINQ works when dealing with in-memory collections of things. In combination with Entity Framework, or another object-relational mapper (ORM), LINQ makes getting data out of your database a ton easier.

When querying a database in a .NET application, you will generally need a database connection object, a database command object, and a data reader. Here's a simple example using SQL Server:

public void DirectQueryExample(string connectionString)
{
	using (var conn = new SqlConnection(connectionString))
	{
		var command = new SqlCommand("SELECT * FROM LookupData", conn);
		conn.Open();
		var reader = command.ExecuteReader();
		foreach (var row in reader)
		{
			Console.WriteLine(reader[0]);
		}
	}
}

(Let's skip for now how bad it is to execute raw SQL from your application.)

With Entity Framework (or another ORM), the ORM generates classes that represent tables or views in your database. Imagine you have an animals table, represented by an animal class in your data project. Finding them in your database might now look like this:

public IEnumerable<Animal> OrmQueryExample(string species)
{
	var result = new List<Animal>();
	using (var db = Orm.Context)
	{
		var dtos = db.Animals.Where(p => p.Species == species);
		result.AddRange(dtos.ForEach(MapDtoToDomainObject));
	}

	return result;
}

private Animal MapDtoToDomainObject(AnimalDto animalDto)
{
	// Code elided
}

That looks a little different, no? Instead of opening a connection to the database and executing a query, we use a database context that Entity Framework supplies. We then execute a LINQ query directly on the Animals table representation with a predicate. The ORM handles constructing and executing the query, and returns an IQueryable<T> of its self-generated Animal data transfer object (DTO). When we get that collection back, we map the fields on the DTO to the domain object we want, and return an IEnumerable<T> of our own domain object back to the caller. If the query comes up empty, we return an empty list. (Here's a decent Stack Overflow post on the difference between the two collection types.)

These are naive examples, of course, and there are many other ways of using EF. For example, for field mapping we might use a package like AutoMapper instead of rolling our own field-level mapping. I encourage you to read up on EF and related technologies.

Comments are closed