Wednesday, 18 June 2014

Dapper : some minor but useful tweaks

At Stack Exchange we pay a lot of attention to our data access, which is why a few years ago we spawned dapper. In case you haven’t seen it, the purpose of dapper is to remove the mind-numbing and easy-to-get-wrong parts of ADO.NET, making it easy to do proper parameterization, without having the overhead of a micro-ORM. In other words, to make it easy to do this:
DateTime from = ...
int id = ...
int count = ...
var orders = connection.Query<Order>(
    @"select top (@count) * from Orders where CustomerId=@id
    and CreationDate >= @from and Status=@open",
    new { id, from, count, open=OrderStatus.Open }).ToList();

It is unapologetic about expecting you to know and “own” your SQL – entirely good things, IMO (by comparison: most professionals wouldn't program the web tier without knowing what their html looks like).

Inline value injection

With a few exceptions the sql that you pass in is essentially opaque: the only things it has done historically is:
  • check the sql to see which possible parameters can definitely be ignored, because they aren’t mentioned (assuming it isn’t a stored procedure, etc)
  • parameter expansion (typically used for in queries, i.e. where id in @ids, where @ids is something like an int[])
Sometimes, however, parameters bite back. There are times when you don’t really want something to be a parameter – it either changes the query so significantly that a separate query plan would be desirable. You don’t want to use a different command-string per value, as this would force dapper to perform meta-programming per value (since it would be a cache miss on the strategy cache).

Alternatively, often the value will never be changed (but is treated as a parameter for code reason), or will only ever be one value unless some configuration setting is changed, maybe twice a year. Classic values here might be a status code from an enum, or the number of rows (select top 50 sometimes performs quite differently to select top (@rows)).

Because of this, the current builds of dapper introduce value injection. This uses an intentionally different syntax similar to parameters, but injects the value directly into the SQL immediately before execution (but allowing dapper to re-use the strategy). It probably makes more sense with an example:
DateTime from = ...
int id = ...
int count = ...
var orders = connection.Query<Order>(
    @"select top {=count} * from Orders where CustomerId=@id
    and CreationDate >= @from and Status={=open}",
    new { id, from, count, open=OrderStatus.Open }).ToList();

Essentially, anything of the form {=name} will be treated as an inlined value, and will be replaced rather than treated as a parameter. Of course, at this point you’re probably screaming “nooooooo! what about sql injection!” – which is why it restricts usage to integer-based types (including enums). This significantly reduces the risk of any abuse, and of course: you don’t have to use it if you don’t want!

Parameter expansion support for OPTIMIZE FOR query hints

Parameter sniffing can be a real pain. For us, we call this “the Jon Skeet problem”: we have some very different users – some with maybe a handful posts and a few dozen reputation, and some with tens of thousands of posts and over a half-million reputation (which means: a lot of vote records). Let’s assume we want to keep the user-id as a regular SQL parameter: if the first use of a query is for “new user Fred”, the query plan won’t work well for Jon. If the first use of a query is for Jon, it won’t work well for “new user Fred”. Fortunately, SQL Server has a mechanism to tell it not to get too attached to a query-plan based on a parameter – the OPTIMIZE FOR query hint. This can be left open (applies to all parameters), but it is often desirable to use the variant that tells it exactly which parameters we are concerned about. This is easy normally, but recall that dapper offers parameter expansion. So how can we use this query hint with the in query above? We don't conveniently know the names of the eventual parameters...

Because of this, dapper now recognises this specific pattern, and performs parameter expansion compatible with this hint. If you use:
option (optimize for (@ids unknown))

it will expand this out to the correct query hint for the parameters that @ids become during expansion.

Async all the things

The usage of async keeps growing, so dapper has now evolved much better async methods (with a number of useful contributions from users); most of these are self explanatory – simply using the *Async method names, but some key additions:
  • Support for cancellation tokens
  • Opt-in use of pipelining (performing a range of operations on a connection without waiting for the early operations to complete – this requires MARS to be enabled)


No huge changes, but hopefully useful to a few people. Enjoy.


Tom Robinson said...

Thanks Marc, will definitely check out the async improvements.

code xbox live gold gratuit said...

Get your free codes of xbox live game here on this site and make your payable game absolutely free Thank you code xbox live gold gratuit

Fangyaya said...

louis vuitton borse
san antonio spurs jerseys
asics running shoes
jordan 3 retro
michael kors bags
coach factory outlet
adidas nmd
cheap pandora jewelry
adidas nmd
cheap oakley sunglasses
polo shirts
lebron 13 shoes
abercrombie outlet
uggs on sale
louboutin shoes
louis vuitton
seattle seahawks jerseys
mont blanc pen
ralph lauren
coach factory outlet
burberry sale
rolex watches
moncler uk
christian louboutin pas cher
cheap ray ban sunglasses
uggs for cheap
rolex watches for sale
christian louboutin sale
moncler outlet
coach outlet
juicy couture
cheap toms
uggs for men
louis vuitton bags
air jordan 8
ray bans
copy watches
polo ralph lauren
uggs on sale
oakley sunglasses

Eric Yao said...

Coach Outlet

Christian Louboutin Shoes

Valentino Shoes

Michael Kors Outlet

Coach Factory Outlet

Coach Outlet Online

Coach Purses

Kate Spade Outlet

Toms Shoes

Hermes Belts

Louis Vuitton

Fendi Handbags

Giuseppe Shoes

Michael Kors Outlet

Stephen Curry Shoes

Salomon Shoes

North Face Outlet

Coach Outlet

North Face Outlet

Burberry Outlet

North Face Outlet

North Face Jackets

Skechers Shoes

Toms Outlet

North Face Outlet

Nike Air Max

Nike Hoodies

Marc Jacobs Handbags

Marc Jacobs Outlet

Jimmy Choo Shoes

Jimmy Choos

Burberry Belt

Louis Vuitton Belt

Salvatore Ferragamo

Marc Jacobs Handbags

Lululemon Outlet

True Religion Outlet

Tommy Hilfiger

Michael Kors Outlet

Coach Outlet

Red Bottoms

Kevin Durant Shoes

New Balance Outlet

Adidas Outlet

Coach Outlet Online

Stephen Curry Jersey

Vans Outlet

Ralph Lauren Outlet

True Religion Outlet

ED Hardy Outlet

North Face Outlet

UGG Outlet

UGG Outlet

North Face Outlet

Ugg Boots Sale

UGGS For Women

Skechers Go Walk

Adidas Yeezy Boost

Adidas Yeezy

Adidas NMD

Coach Outlet