Tables and Linq to SQL

[nitro52]

I have a scenario where i need to update some fields in a SQL table so
in my Scenaro outline i pass the step a table with the values of the
fields that need updating.

What i noticed is that this can be rather painful because you need to
specify and set each field.

What i want to be able to do is Select the record that i want to
update like

Contact contact = (from c in dataContext.Contacts
select c).FirstOrDefault();

If i have a contact in this state is there an easy way to update a
bunch of the properties rather than iterating through the table and
setting each property one by one?

[Darren Cauthon]

I have good news and bad news.

First, the good news: Not even two weeks ago, I submitted a pull
request for an update that handles this. I created an extension
method off of table called FillInstance<T>(T) that will do this for
you.

So if you have a table like this:

| Field | Value |
| FirstName | John |
| LastName | Galt |

You could do this:

var contact = dataContext.Contacts.First();
table.FillInstance(contact);

and the FirstName would be set to "John" and the LastName would be set
to "Galt." Any property on contact that was not defined in the table
would be left alone.

So now here's the bad news: Since I just wrote it, it's not in a
released build. If you really need this now, you can go to the branch
at the link below, download the source, and compile your own version.
However, it has not been merged into master yet, and I don't know if
it will be in the next released version, so you'll just have to be
aware that you're deviating from the main line.

https://github.com/techtalk/SpecFlow/tree/fill_instance

Does that help?


Darren

[nitro52]

Thats awesome, i don't really need this strait away so thats fine. I
guess the only downside to that is if you have badly described sql
column names then you need to have the same name in your specflow
feature. I guess we just have to live with that though. also there are
cases where in the database we might set a numerical representation
where in the specflow feature it would make more sense to use an
english word. maybe an overload to exclude certain properties would be
nice?

[Gáspár Nagy]

there will be a release within two weeks, where these improvements
will be included.

[Darren Cauthon]

Hi Nitro,

I think badly-described sql column names are between you and your
ORM. :) NHibernate and Entity Framework both have ways of changing
the mappings between badly-named columns into appropriate names in
your object. For example, "JunkyJunk123Niner" could map to
"FirstName" in your object... if you wanted.

I think if you're in the situation where you want to use an English
word instead of a number, this method won't work well for you. At
least without some table manipulation in the step definition itself.


Darren

[nitro52]

I took your advise in regards to the ORM mapping names, not sure why i
didn't think of that. I also downloaded the branch you suggested to
try out the FillInstance extension method. After some use i found a
few things

It can be very fragile, if there is a problem converting any property
you can't just handle the exception and manually update it later
because it stops filling the instance as soon as it hits an
exception.

To me it makes a lot of sense to have your Feature files readable and
understandable by everyone not just developers that understand the
underlying code. Because of this i think its important to use english
words and then convert them to the correct logical value in your unit
test. For example he have a numeric system (0,1,2,3) that represents a
date period in our system. in the feature it would make more sense to
say Weekly, Fortnightly, Four Weekly and Monthly.

To get around this i played with the idea of having a PropertyOverride
that you pass to the FillInstance method. I ended up with this method

private static void LoadInstanceWithKeyValuePairs<T>(Table
table, T instance, List<PropertyInfo> overrideProperties)
{
var handlers = GetTypeHandlersForFieldValuePairs<T>();

var propertiesThatNeedToBeSet = from property in
typeof(T).GetProperties()
where !
overrideProperties.Contains(property)
from key in handlers.Keys
from row in table.Rows
where
key.IsAssignableFrom(property.PropertyType)
&&
IsPropertyMatchingToColumnName(property, row["Field"])
select new { Row = row,
property.Name, Handler = handlers[key] };

propertiesThatNeedToBeSet.ToList()
.ForEach(x => instance.SetPropertyValue(x.Name,
x.Handler(x.Row, x.Row["Value"])));
}

I think this worked by i then struck another issue. Because of this
bug with specflow https://github.com/techtalk/SpecFlow/issues#issue/44
i had to start entering values in for fields that might be null. In
this case i entered the word Null and then had an extension method for
a string that would convert it into the correct nullable type. The
FillInstance method however does not understand this and it will cause
it to fail. I then played around with a typeHandlerOverrides parameter
where you could override the typehandler for the specified type and
point it to a new method. but my lack of knowledge seemed to hit a
problem. I was passing it a this

var typeHandlerOverrides = new Dictionary<Type,
Func<TableRow, string, object>>
{
{typeof (int?), (TableRow row,
string id) => "Value".ToIntNullable()},
{typeof (decimal?), (TableRow
row, string id) => "Value".ToDecimalNullable()},
{typeof (DateTime?), (TableRow
row, string id) => "Value".ToDateTimeNullable()},
};
table.FillInstance(contact, overrideProperties,
typeHandlerOverrides);

This didn't seem to change anything though, i think the problem is
that specflow dosn't have the correct references to access my custom
converter methods.

Either way i think the FillInstance still needs a way you can create
customer type converters and custom property converters (because
sometimes just a type conversion is not logical). I think you might be
able to create some converter type in speflow and then from that but
i'll have to have a ponder over this. Any thoughts?

[Darren Cauthon]

Hi Nitro,

Yeah, if the FillInstance (or CreateSet, or CreateInstance) method is
passed a table with a property that isn't one of the base types that
are handled, it will throw an exception. I never thought that by
ignoring properties it could map, it would make it easier for someone
to come in after-the-fact and set the values manually. Hmm... I'll
have to sleep on that one. It could be nice, or it could be
disruptive for someone to type in a value and not know that the
setting of the value failed.

Again, I have good news and bad news. Bad news first, there is no way
to provide a custom handler. However, the good news is that such a
thing should be easier to implement in the near future. Due to some
polite prodding from Marcus on Twitter, I refactored the "instance"
and "set" logic so that they use the same logic for creating and
comparing objects.

What does that mean for you? It means that after the refactoring, I
was left with a one simple dictionary that contains the methods for
handling each of the types the Assist methods handle (http://bit.ly/
hJay4I). Now that it is refactored down to this point, it wouldn't be
too difficult to make this source extendable.

I've been thinking about this for a week or two, but I've been
slightly leaning against making it extendable because it will add a
layer of complexity that, as far as I can tell, is unnecessary for 98%
of use cases. Of course, I'm only seeing *MY* use cases, but I use
SpecFlow daily and in many different situations and it works fine for
me. Plus, the Assist methods were only meant to be helpers; tools
that you can use to speed up your development -- not coverage of every
possible scenario that might involve a table. But SpecFlow is an open-
source project, so if others have a better idea....

Your situation might be one where it might be best to just stick with
mapping the table manually. Hey, it wasn't too long ago when that's
what we were all doing anyway. :)


Darren

> bug with specflowhttps://github.com/techtalk/SpecFlow/issues#issue/44

[nitro52]

Hmm i see what you mean. before the idea of overriding the property I
was hoping that i could throw a try catch block around the
FillInstance method and then manually handle the exception. Problem
there is the exception it throws would need to contain enough info to
identify the problem property and that it stops converting once it
hits that exception. I couldn't see anywhere that i could use within
the exception that would pinpoint the exact problem property. This
method probably isn't ideal but then again if it was extensible maybe
you wouldn't need to change this.

Out of interest who writes your Feature Files in your company? The
problem i find is that our entities we are working with contain a lot
of different data and the scenario you are testing may not cover all
of those properties. A FillIntance option for us would reduce code
clutter in the Step definitions a lot. The average scenario i'm
working on at the moment can contain about 10 or so fields that i need
to pull out of a table, store in a temp object before updating the sql
entity with those properties. It would really be helpful to not have
to do this for every scenario. If i can make my life easier i'd
defiantly look into that option. If the FillInstance option works for
you it can reduce many lines of code into a single line, thats a huge
benefit in my opinion.

I guess the big difference is that my scenarios are very much data
driven so it requires me to interact with the database directly
instead of interacting with objects in memory. So the types are
Dependant on the database types. also the application is very mature
so redesigning the database structure is not an option.

> > > > > > > If i have...
>
> read more »

[Darren Cauthon]

Hi Nitro,

At my work, I write the feature files. I'm also one of the few who
use SpecFlow, but it's slowly starting to spread. And project
managers are starting to appreciate that I can tell them what my code
does in a format they can understand.

Unless someone has any other thoughts, I think it's probably ok to
fill the object as much as possible before throwing the exception. I
think the exception is still a good thing, since you need to know
somehow if you enter something that can't be handled, but at least it
would give you a possible solution without affecting the current
functionality.

Could you enter it into GitHub as a feature request? Tag it with
@darrencauthon so I'll get a notification (i.e. continual reminder)
about it.


Darren

> > > > > > > I have good news and bad news....
>
> read more »

[nitro52]

Hi, i logged an issue but i'm not sure if i did it correctly, i
couldn't see anywhere to set it as a feature-request or add a tag.
sorry first time using Github

> > > > > I think if you're in the situation where you want to use an English...
>
> read more »

[Gáspár Nagy]

no prob. i have tagged it accordingly

> ...
>
> read more »