{"id":1132,"date":"2012-01-31T17:00:00","date_gmt":"2012-01-31T16:00:00","guid":{"rendered":"http:\/\/blog.eweibel.net\/?p=1132"},"modified":"2012-01-31T13:26:00","modified_gmt":"2012-01-31T12:26:00","slug":"dataset-and-deleted-rows","status":"publish","type":"post","link":"https:\/\/blog.eweibel.net\/?p=1132","title":{"rendered":"DataSet and deleted rows"},"content":{"rendered":"

\"Pair<\/a>Yes, I know, the DataSet isn’t the leading edge technology, but as the company where I work currently, there are several companies who use DataSet<\/a> as data access technology.<\/p>\n

Recently, I had to migrate the DataSet subclasses of the framework of my current employer to .NET 4.0 and add LINQ<\/a> support. After this migration I made some unit tests and I found a surprising fact: If you using LINQ with a DataSet, then you have to handle deleted rows, which isn’t as you would expecting the behaviour.<\/p>\n

First, here a little setup to run the tests:<\/p>\n

\n
TestDataSet ds = new TestDataSet();\nTestDataSet.BECRow row = ds.BEC.NewBECRow();\nrow.BecId = Guid.NewGuid();\nrow.RunDat = DateTime.Now;\nrow.MachineName = Environment.MachineName;\nrow.CrtDat = DateTime.Now;\nrow.CrtUsr = \"pw\";\nds.BEC.AddBECRow(row);\nds.AcceptChanges();\n<\/pre>\n<\/div>\n
The old way<\/strong><\/p>\n
In the ages before LINQ you get the preferred rows in the following way out of a typed or untyped DataSet:<\/p>\n
\n
string strFilter = string.Format(\"{0} = '{1}'\", ds.BEC.CrtUsrColumn.ColumnName, \"pw\");\nTestDataSet.BECRow[] rowsBEC = (TestDataSet.BECRow[]) ds.BEC.Select(strFilter);\nConsole.WriteLine(\"Row count: {0}\", rowsBEC.Length);\n<\/pre>\n<\/div>\n
As you expecting, the result here is one row. The ugly thing is the magic filter string and the casting for the variable rowsBEC. But if you’re using DataSets, you are used to such code.<\/p>\n
The new way?<\/strong><\/p>\n
With LINQ, the code could look much nicer:<\/p>\n
\n
var query = from r in ds.BEC\n\t\t\twhere r.CrtUsr == \"pw\"\n\t\t\tselect r;\nConsole.WriteLine(\"Row count: {0}\", query.Count());\n<\/pre>\n<\/div>\n
That’s better and I thought that I had the job done. But I was wrong, seriously wrong. This code behaves differently because you will get also the deleted rows (when no where clause exists) or even worse get a DeletedRowInaccessible<\/a> exception. This exception occurs, when you try to access a property on a deleted row, which will be the case here, if there are deleted rows in the DataTable<\/a>.<\/p>\n
Why? Well, if you look at the code of the Select method (for example with the tool Reflector<\/a>) you will see, that there is an implicit filtering on the current rows (added, modified and unchanged).<\/p>\n
The solutions<\/strong><\/p>\n
So, how could you fix this issue? There are at least two ways. First, the nearest one:<\/p>\n
\n
var query = from r in (TestDataSet.BECRow[]) ds.BEC.Select()\n\t\t\twhere r.CrtUsr == \"pw\"\n\t\t\tselect r;<\/pre>\n<\/div>\n
This solution works, but you have to do the ugly cast. Well, because we have a framework and we subclassed every DataSet class, we could fix that by an override in the generic base class.<\/p>\n
If you don’t have a framework, where you subclassed all the DataSet classes, then there is an alternative way:<\/p>\n
\n
var query = from r in ds.BEC\n\t\t\t where r.RowState != DataRowState.Deleted && r.CrtUsr == \"pw\"\n\t\t\t select r;<\/pre>\n<\/div>\n
The order here is important, because you have to check first the RowState and after that you can add the real where clause. Also the "&&" is important, because it has to break if the row is deleted.<\/p>\n
Conclusion<\/strong><\/p>\n
There are some pitfalls with DataSet and LINQ, because you wouldn’t expect that behaviour with deleted rows. It doesn’t make any sense at all, because I nearly never saw a reason why my business logic would like to deal with deleted rows. Also the behaviour is unnatural because you think of the DataSet as an in-memory-database, and on the database you have not to deal with deleted rows, unless you have to implement a trigger.<\/p>\n","protected":false},"excerpt":{"rendered":"
Yes, I know, the DataSet isn’t the leading edge technology, but as the company where I work currently, there are several companies who use DataSet as data access technology. Recently, I had to migrate the DataSet subclasses of the framework of my current employer to .NET 4.0 and add LINQ support. After this migration I made some unit tests and I found a surprising fact: If you using LINQ with a DataSet, then you have to handle deleted rows, which…<\/p>\n
 Read More  Read More<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[13],"tags":[],"class_list":["post-1132","post","type-post","status-publish","format-standard","hentry","category-net"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/plOV9-ig","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":677,"url":"https:\/\/blog.eweibel.net\/?p=677","url_meta":{"origin":1132,"position":0},"title":"Round-up of a data centric architecture","author":"Patrick","date":"11. Apr 2010","format":false,"excerpt":"In my last big project we had to use a data centric architecture. There was a learning curve which architecture was the most appropriate one. The result is visible in the picture bellow: Lets explaining the diagram. The data (or state) is managed by the database layer and the common\u2026","rel":"","context":"In "Software architecture"","block_context":{"text":"Software architecture","link":"https:\/\/blog.eweibel.net\/?cat=4"},"img":{"alt_text":"Architektur","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Architektur_thumb.jpg?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Architektur_thumb.jpg?resize=350%2C200 1x, https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Architektur_thumb.jpg?resize=525%2C300 1.5x"},"classes":[]},{"id":9,"url":"https:\/\/blog.eweibel.net\/?p=9","url_meta":{"origin":1132,"position":1},"title":"Filtering on DataTables","author":"Patrick","date":"2. May 2007","format":false,"excerpt":"When you want to apply a filter to your datatable in your dataset, you can use the method Select on the class DataTable. In our current project we use this method very often, also for databinding. Recently I discover an unexpected behaviour. Let\u2019s assume, that our datatable has 4 datarows\u2026","rel":"","context":"In "Good practices"","block_context":{"text":"Good practices","link":"https:\/\/blog.eweibel.net\/?cat=5"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":160,"url":"https:\/\/blog.eweibel.net\/?p=160","url_meta":{"origin":1132,"position":2},"title":"When to use stored procedures","author":"Patrick","date":"13. May 2009","format":false,"excerpt":"Recently I discussed with a colleague when to use stored procedures. As exptected it was quite a religious conversation. A few days later I found the following screencast: The Pros and Cons of Stored Procedures Based on the discussion and the screencast I tried to summarize my Pros and Cons:\u2026","rel":"","context":"In "Good practices"","block_context":{"text":"Good practices","link":"https:\/\/blog.eweibel.net\/?cat=5"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":17,"url":"https:\/\/blog.eweibel.net\/?p=17","url_meta":{"origin":1132,"position":3},"title":"Get the size of your tables","author":"Patrick","date":"17. Jan 2008","format":false,"excerpt":"Recently my boss asked me, why the databases (Microsoft SQL Server 2005) of our customers are so big. With the following SQL-Statements I could give more or less an answer to my boss. CREATE TABLE PW_SPACE( name varchar(255), rows int, reserved varchar(255), data varchar(255), index_size varchar(255), unused varchar(255) ) GO\u2026","rel":"","context":"In "First experiencies"","block_context":{"text":"First experiencies","link":"https:\/\/blog.eweibel.net\/?cat=7"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1154,"url":"https:\/\/blog.eweibel.net\/?p=1154","url_meta":{"origin":1132,"position":4},"title":"TF.exe or maybe the most useful tool for TFS","author":"Patrick","date":"31. Jan 2012","format":false,"excerpt":"When you've to use TFS, then there are moments when you're missing some features in the UI tools in Visual Studio. In those situations the console tool tf.exe is very useful. In this short blog post I summarized the commands I used the most. Update an old version of a\u2026","rel":"","context":"In ".NET"","block_context":{"text":".NET","link":"https:\/\/blog.eweibel.net\/?cat=13"},"img":{"alt_text":"visualstudio","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/visualstudio_thumb.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":797,"url":"https:\/\/blog.eweibel.net\/?p=797","url_meta":{"origin":1132,"position":5},"title":"NHibernate day in Bologna","author":"Patrick","date":"25. Oct 2010","format":false,"excerpt":"During a whole day several speakers spoke about NHibernate and related topics. This conference was in Bologna and was very well prepared and organized. You could watch the slides and the videos of the sessions here. I joined the following sessions: Keynote Simone Chiaretta opened the conference and showed during\u2026","rel":"","context":"In ".NET"","block_context":{"text":".NET","link":"https:\/\/blog.eweibel.net\/?cat=13"},"img":{"alt_text":"NHDay_3","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/NHDay_3_thumb.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts\/1132","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1132"}],"version-history":[{"count":8,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts\/1132\/revisions"}],"predecessor-version":[{"id":1147,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts\/1132\/revisions\/1147"}],"wp:attachment":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1132"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1132"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}