creating ExecuteQuery method

[Marc Gravell]

For info I have e-mailed a version that uses SqlMetal's
ColumnAttribute to demonstrate mapping. If anybody else is interested
e-mail me ;-p

Crude benchmarks show it slightly faster than raw LINQ-to-SQL, but it
is doing a lot less:
* no change tracking
* no composability
* only homogeneous sets supported
etc

It made an interesting diversion, but I can't think of many cases when
I would use this instead of a data-context (ideally EF when RTM).

Marc

 

[Andrus]

Marc,

Here's another version
* does 2-pass match as described
* handles null reference-type values (string, byte[])
* handles null Nullable<T> value-type values (int? etc)
* handles (for entity T) class, struct and Nullable<struct>
* caches and re-uses compiled delegates (thread-safe)

Thank you very much.
I did minor testing and commited your code.

Andrus.

 

[Andrus]

Marc,

For info I have e-mailed a version that uses SqlMetal's

Thank you.
I encountered two issues:

1. I need probably for WinForms GUI to trim trailing characters from CHAR
type columns and apply custom encoding conversion for all CHAR database
columns whose lenght is creater than 1.

In application init I'm planning to use

Vendor.StringConversion += (stringConversionEventArgs)=>
stringConversionEventArgs.Data.ToString().TrimEnd();

Code which implements this is:

class Vendor {

public class StringConversionEventArgs {

public object Data;
public Type Type;
public IDataRecord DataRecord;

public StringConversionEventArgs(object data, Type type, IDataRecord
dataRecord) {

Data = data;
Type = type;
DataRecord = dataRecord;
}
}

public static event Action<StringConversionEventArgs> StringConversion;

static string OnStringConversion(object data, Type type, IDataRecord
dataRecord) {

StringConversionEventArgs stringConversionEventArgs =
new StringConversionEventArgs(data, type, dataRecord);

if (StringConversion != null)
StringConversion(stringConversionEventArgs);
return stringConversionEventArgs.Data.ToString();
}
}

Unfortunately I do'nt know hot to add OnStringConversion() method call to
your code.
Also I don't know is this best design pattern to add global conversion
possibility.

2. It does not read properties without ColumnAttribute . MSDN describes
that it should:
<quote>
If a field or property is not mapped, a column with the same name as the
field or property is expected in the resultset.

Crude benchmarks show it slightly faster than raw LINQ-to-SQL, but it
is doing a lot less:
* no change tracking

I think it is possible to use Attach() method to add returned entities to
change tracking.
I do'nt know how to determine is the passed type part of DataContext or not.
I havent found DataContext method like

bool BelongsToThisDataContext( Type entity)

which can be used to test is Attach() valid. So I'm plannig to use crude
try/catch to add returned entites to DataContext in
some other method.

It made an interesting diversion, but I can't think of many cases when
I would use this instead of a data-context (ideally EF when RTM).

I'm planning to use it for queries against tables in database metadata.
I need to get list of available schemas and estimated number of records.

AFAIK , no one DLinq provides nor EF does not provide way nor generate
classes to access database metadata tables and views.

So ExecuteQuery<>() is the only way without falling back to DataSets.

Also this method allows to create entity type dynamically from script and
use this method to fill this dynamic entity with data.

Andrus.

 

[Marc Gravell]

Both issues mentioned are solveable - although I'd probably do the
conversion thing a little differently.... I'll see what I can do on
the train tomorrow...

Marc

 

[Marc Gravell]

A simple option for your first point (translated columns) would be to
have a facade property that does the translation?

example:

Column(Name="ShipCountry", Storage = "_ShipCountry", DbType =
"NVarChar(15)")]
public string ShipCountryRaw {.......}

public string ShipCountry // could be any Type
{
get { return Reverse(ShipCountryRaw); }
set { ShipCountryRaw = Reverse(value); }
}
private string Reverse(string value) {
if(value == null || value.Length <= 1) return value;
char[] buffer = value.ToCharArray();
Array.Reverse(buffer);
return new string(buffer);
}

This seems to work well, and ShipCountryRaw is used correctly when
reading and writing (either mechanism), and allows WHERE in LINQ-to-
SQL etc based on ShipCountryRaw (but not on our facade ShipCountry).

As an alternative to the above (but less robust) - have you tried
using a non-default Storage? i.e. set Storage to a private property
that does the translation... seems to work fine when *loading* data
for both LINQ-to-SQL and the code as posted (without changes)... but
unfortunately LINQ-to-SQL doesn't seem to use Storage when using
SubmitChanges() [or when building WHERE clauses, but that is more
reasonable]. For these 2 reasons I don't recommend this option.

example:

[Column(Storage = "DbShipCountry", DbType = "NVarChar(15)")]
public string ShipCountry {......}

private string DbShipCountry
{
get { return Reverse(_ShipCountry); }
set { _ShipCountry = Reverse(value); }
}

--------

For your second point (unmapped columns), I was able to do this with
minimal changes:

In CreateInitializer, at the top of the ordinal loop:

string name = names[ordinal];
BindingInfo bindingInfo;
if (!TryGetBinding(name, out bindingInfo))
{ // try implicit binding
MemberInfo member = GetBindingMember(name);
if(member == null) continue; // not bound
bindingInfo = new BindingInfo(true, member);
}

where GetBindingMember is defined in InitializerCache<T> as (note:
moved FLAGS and PROP_FIELD out to the class itself):

const BindingFlags FLAGS = BindingFlags.Instance |
BindingFlags.Public | BindingFlags.NonPublic;
const MemberTypes PROP_FIELD = MemberTypes.Property |
MemberTypes.Field;

private static MemberInfo GetBindingMember(string name)
{
Type type = typeof(T);
return FirstMember(type.GetMember(name, PROP_FIELD, FLAGS))
?? FirstMember(type.GetMember(name, PROP_FIELD, FLAGS |
BindingFlags.IgnoreCase));
}

and used GetBindingMember from the static cctor (search on the
comment):

// locate prop/field: case-sensitive first, then
insensitive
storageMember = GetBindingMember(storage);

Oh; I think I also made the comparer in "readers" case-insensitive.

Marc

 

[Marc Gravell]

One other thing - the DataContext.ExecuteQuery<T> method accepts
parameters in string.Format format... propose tweak as below (focusing
on the handling of "parameters" and subsequent CommandText).

Marc

static IEnumerable<T> ExecuteQuery<T>(string command, params
object[] parameters)
{
if (parameters == null) throw new
ArgumentNullException("parameters");

using (DbConnection conn = new SqlConnection(CS))
using (DbCommand cmd = conn.CreateCommand())
{
string[] paramNames = new string[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
paramNames = "@p" + i.ToString();
DbParameter param = cmd.CreateParameter();
param.ParameterName = paramNames;
param.Value = parameters ?? DBNull.Value;
cmd.Parameters.Add(param);
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(command, paramNames);
// SNIP everything else "as was"

 

[Andrus]

Marc,

One other thing - the DataContext.ExecuteQuery<T> method accepts
parameters in string.Format format... propose tweak as below (focusing
on the handling of "parameters" and subsequent CommandText).

Thank you.
I use actually a bit changed code as shown in the SVN link I posted earlier.
This code includes call to special common method
Executequery_preparaparametes which performs proper parameter substitution.
So this is not an issue for me.

Andrus.

 

[Andrus]

Marc,

It made an interesting diversion, but I can't think of many cases when
I would use this instead of a data-context (ideally EF when RTM).

I addition to previus goodie (global custom conversion support)
your version also allows to retrieve partial properties of object without
using your double-projection
extension method as discussed earlier, e.q:

1. Retrieve only customer name to customer object using your
ExecuteQuery<Customer>() (MS version fails probably in this case according
to MSDN doc).
2. Attach() retrieved Customer objects to DataContext
3. Now we can Update name and Delete customers.

Using pure MS code to perform same operations requires to retrieve all
customer properties from db and is thus a magnitude slower.

Andrus.

 

[Andrus]

Marc,

A simple option for your first point (translated columns) would be to
have a facade property that does the translation?

This seems to work well, and ShipCountryRaw is used correctly when
reading and writing (either mechanism), and allows WHERE in LINQ-to-
SQL etc based on ShipCountryRaw (but not on our facade ShipCountry).

All columns in my database are fixed length CHAR(n) types.
I need to trim trailing spaces from all database CHAR columns.
Mainly this is required prevent DataDridView displaying three dots ... in
end of narrow columns (I havent found a way to turn this off).

For accented characters I need to apply custom conversion to UTF to make
them propery visible in GUI since they are retried in non-unicode format
from db.

Using your approach for this requires:

1. Use surrogate property names in every place of my code, eq.
ShipCountryRaw instead of ShipCountry
2. Creating custom SQLMetal which generates those surrogate properties
statically.

So it seems that using this is not reasonable.

Only solution I know is before yield return statement in your code :

1. Loop over all string properties
2. Invoke conversion event as described in my previous message separately
for every string property.

This requires to use reflection so probably decreases perfomance.

As an alternative to the above (but less robust) - have you tried
using a non-default Storage? i.e. set Storage to a private property
that does the translation... seems to work fine when *loading* data
for both LINQ-to-SQL and the code as posted (without changes)... but
unfortunately LINQ-to-SQL doesn't seem to use Storage when using
SubmitChanges() [or when building WHERE clauses, but that is more
reasonable]. For these 2 reasons I don't recommend this option.

example:

[Column(Storage = "DbShipCountry", DbType = "NVarChar(15)")]
public string ShipCountry {......}

private string DbShipCountry
{
get { return Reverse(_ShipCountry); }
set { _ShipCountry = Reverse(value); }
}

Storage doc from

http://msdn2.microsoft.com/en-us/library/system.data.linq.mapping.dataattribute.storage.aspx :

wrote:

Gets or sets a private storage *field* ....

So Storage = "DbShipCountry" is not allowed in normal DLinq.

Also I think that this has also the same issues as the first approach.

It may be possible to add conversion property like Frans wrote is
implemented in LLBLgen .
But this also requires changing SQLMetal go generate constant property for
every string column an is thus not reasonable.

For your second point (unmapped columns), I was able to do this with
minimal changes:

I applied those changes, moved all code to separate file and marked you as
author in this file.
Should I publish a link to this file in SVN here ?

Oh; I think I also made the comparer in "readers" case-insensitive.

Currently I need only case insensitive match. So I havent tested case
issues.

Andrus.

 

[Marc Gravell]

My concern is how to introduce a conversion into the pipeline without
crippling things. At one level it would be nice to have it very granular so
that only the properties you care about get converted, but this is at odds
with your need here...

 

[Marc Gravell]

I've e-mailed a variant that allows an event on the context instance. Seems
to work; still quicker than LINQ-to-SQL ;-p

Marc

 

[Marc Gravell]

Ignore "quicker" - I was testing on too small a data-set. The event does get
in the way; I'll e-mail yet another version that considers this... (sheesh)

Marc

 

[Andrus]

Marc,

Ignore "quicker" - I was testing on too small a data-set. The event does
get in the way; I'll e-mail yet another version that considers this...
(sheesh)

Thank you.
I performed minor test, moved conversion class to IVendor and committed it.

http://dblinq2007.googlecode.com/svn/trunk/DbLinq/Vendor/Implementation/ExecuteQuery.cs

Andrus.

 

[Andrus]

Marc,

Ignore "quicker" - I was testing on too small a data-set. The event does
get in the way; I'll e-mail yet another version that considers this...
(sheesh)

Some thoughts:

1. ConvertValue event appears to be thread-unsafe (since it uses a member
field as argument event).

2. The method also makes use of reflection to get method names: why not to
use lambda, since this allow refactoring (if a method name changes, the
lambda will follow, but the literal strings describing the method may not).
There are some samples in DLinq RowEnumerator<> (where reflection is
replaced by lambdas)

Andrus.

 

[Marc Gravell]

1. ConvertValue event appears to be thread-unsafe (since it uses a member

field as argument event).

Do you mean the bit where I'm re-using the event-arg? Well, it is
limited to a single context, and I don't know whether an individual
data-context promises thread safety, but yes: I suppose it might be
better to build a shim object that holds the event-arg and presumably
also the event delegate itself - and call the On"..." from that shim,
not the context. My intent was to avoid creating huge volumes of gen-0
event-arg objects.

2. The method also makes use of reflection to get method names: why not to
use lambda

If you mean for the methdos known at compile-time, then yes; I have
myself posted "infoof" implementations using lambdas; but I was simply
trying to use least complexity:
http://groups.google.co.uk/group/mi...57a9dc5168d/4805324df6b30218#4805324df6b30218