This is the first of a number of posts derived from the documentation for Simple.Data that I’m compiling and writing at the moment. These are less formal and more compact. Hope you like them.

N.B. This is all based on upon code in the Simple.Data.ADO provider upon which most providers are dependent. The exception to this are the MongoDB, oData and Azure Tables providers, so if you’re using these, your mileage will vary. There’s a diagram of Simple.Data requirements and dependencies here for reference.

One of the most obvious plus points of Simple.Data is that it interprets method and property names at runtime and maps them to table, view and column names in your underlying data-store. In this post, I’m going to run through how Simple.Data does all this and how you can influence it all.

If I want to find all the albums in a table called Album with GenreId equal to 1, I can call any of the following and get the same result

db.dbo.Albums.FindAllByGenreId(1);
db.dbo.Album.FindAllByGenreId(1);
db.Albums.FindAllByGenreId(1);
db.Album.FindAllByGenreId(1);

The method convention is as follows.

1. All method calls start with a reference to the database which you generate by calling one of the four Database.Open() methods. That’s the db here.
2. If you need to specify a schema for your table, add that in next. It’s optional though.
3. The primary table or view in your query is specified next.
4. Finally comes the dynamic method name and resolving column names. (More on this in a bit. Table resolution first. )

In a nutshell, that’s

DatabaseReference.[schema_name.]Table_or_View.method();

When the database is eventually queried for data (Simple.Data is late-binding by default), Simple.Data retrieves a list of tables and views from INFORMATION_SCHEMA.TABLES and tries to find the one prefixing the method call using the following rules:

1. It tries an exact match first
2. Then it strips out all non-alphanumeric characters and  tries a case-insensitive match on what’s left
3. Then a pluralized version if the string ‘is not a plural’
4. Finally a singularized version if the string ‘is a plural’

Step 1 is obvious, and hopefully we all know what the tables and columns we’re querying are actually called.

Step 2 is a bit more subtle but very handy for those table and column names which aren’t valid characters for objects in C#.  Take for example, this (abbreviated) exchange on the Simple.Data google group on Jun 12.

I want to query data in a column prefixed with __$, but
db.schema.table.FindBy(__$start_lsn:22); is invalid.

Just use db.schema.table.FindBy(startIsn:22);
Simple.Data will resolve the internal database name from that.

This bears repeating. If your table/column name isn’t an exact match for something in the database, Simple.Data will homogenize (read, run a regular expression over) the name to lower case it and remove non-alphanumeric characters, then it does the same for all the table/column names in the database and tries to find a match.

N.B. This utility also means that Simple.Data will fall over in the edge case where two column names differ only by non-alphanumeric characters, such as its_how and IT_show. Hopefully you don’t come up against this issue (#198) but if you do, you’ll need to tweak FindColumnWithName using reflection to get around it at the moment.

Finally, step 3 and 4 try out pluralized and singularized forms of your table\column name to see if they match. Which leads to the question, “How does it know what plural or single means?” The answer to which is “Pluralizers”, but we’ll wrap up here before we get to those.

In conclusion then, Albums.GenreId will match all of the following

• Albums.GenreId
• Album.GenreId
• ALBUMS.GENREID
• ALBUM.GENREID
• [ALBUMS].[GENREID]
• [ALBUM].[GENREID]
• AlBuM.geNReId   <- mixed case
• Al__*bum.Genr-eId   <- non-alphanumeric characters in column name

Furthermore, you can use your knowledge of this in writing your calls to Simple.Data. For example, we’ve already covered that db.schema.table.FindBy(__$start_lsn:22); is invalid, but there are several valid alternatives, not just one.

db.schema.table.FindBy(startIsn:22); 
db.schema.table.FindByStartIsn(22);
db.schema.table.Find(startIsn == 22);
db.schema.table.Find(db.schema.table.startIsn == 22);

And that’s pretty neat. Simple.Data will deconstruct ay calls to FindBy* and FindAllBy* methods at runtime and resolve any column names in the method name according to the rules above as well.

Pluralizers

So now to the last hanging question. How does Simple.Data know if a word “is plural” or “is single”?

Pluralizers are the classes Simple.Data uses to help it resolve the names of Tables and Views being accessed. The SimplePluralizer class used as a default by Simple.Data just adds/removes the letter ‘s’ to the target string. (Which means incidentally, that if your album name is already a plural, for example Albums, you can misspell it with a double s, Albumss, and it will still work).

To create your own pluralizer, create a class that implements the Simple.Data.IPluralizer interface and call the static Database.SetPluralizer method.

public interface IPluralizer
{
bool IsPlural(string word);
bool IsSingular(string word);
string Pluralize(string word);
string Singularize(string word);
}

For example, to create a new Pluralizer that uses the Entity Framework’s Pluralization service, we have this code, as hidden away in Mark’s unit tests.

class EntityPluralizer : IPluralizer
{
private readonly PluralizationService _pluralizationService =
PluralizationService.CreateService(CultureInfo.CurrentCulture);
public bool IsPlural(string word)
{
return _pluralizationService.IsPlural(word);
}
public bool IsSingular(string word)
{
return _pluralizationService.IsSingular(word);
}
public string Pluralize(string word)
{
bool upper = (word.IsAllUpperCase());
word = _pluralizationService.Pluralize(word);
return upper ? word.ToUpper(_pluralizationService.Culture) : word;
}
public string Singularize(string word)
{
return _pluralizationService.Singularize(word);
}
}

Then you need to call SetPluralizer().

Database.SetPluralizer(new EntityPluralizer());

There are several other pluralization services you could plug in mentioned here as well as rolling your own. Just follow the sample code for the EntityPluralizer in Mark’s unit tests to see how to implement them.

Summary

Simple.Data has a simple four step process to try and match the table and column names you send it to those actually in the database. It is quite powerful and can be altered slightly by creating your own Pluralizer class or by changing the way it homogenizes names using reflection.