Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Confusing error while using an EntityDataSource with a GridView update

Linq-to-Entities baffled me yet again today. I was simply trying to bind an EntityDataSource to a GridView then do an update. It worked as expected, even with the foreign keys... but that was only if I used the auto-generated columns. When I tried to make the fields myself with TemplateFields, all hell broke loose and this error popped up.

While this may make sense to some people, it made none to me, so I basically just started changing things one-by-one until something broke. When I changed AutoGenerateColumns to 'false' it would break. But... if I added in a BoundField that had the primary key in it, it would work? Ok, great, but I didn't want the user to see the primary key field, they don't care about it, so I set that BoundField to Visible="false"; it broke again.

solution

You need to explicitly include a BoundField of the primary key! Since I didn't want the user to see my primary key, I had to resort to hiding it via css:

<asp:BoundField DataField="my_primary_key" ItemStyle-CssClass="hidden" HeaderStyle-CssClass="hidden" />


Where 'hidden' is a class in css that has it's display set to 'none'. Not sure why Entities can't figure this out like all the other DataSources...

Multiple databases in Linq-to-Entities?

Yeah... good luck with that

Yet another HUGE downfall of L2E was found today in my forced learning of this (quickly becoming dreaded) technology.  I have several databases that may need to connect to one central db that was written ages ago with a classic ASP program.  It is basically voodoo magic to us, so we don't touch it, just pull data out of there as we need it.  In the past, if I wanted to do such things with L2S, I would simply drag the table onto my DBML from my Server Explorer and use it like any other Linq-to-SQL object.  Not the case with L2E, apparently it is completely 'out of scope'.  In fact it looks as if you basically have to do some sort of magical workaround to get this to somewhat work (and forget about updating the edmx file automatically).

 

So, regretably, I have been reduced to using oldschool ADO with DataReaders, SqlConnections and SqlCommands.  Why do you make me hate you so L2E, why?  I hope MS is taking notice of these many shortcomings!

You might be making a lot of unneccessary trips to your DB with Linq-to-SQL

Linq-to-SQL has great use of relations, but lazy loading can cause a huge amount of trips to your DB - implementing eager loading is one answer

After my rant on L2E, Dan from Microsoft got back to me in the comments and pointed out a lot of what I wanted to know. One thing that he brought up was the use of eager loading which I hadn't looked in to; turns out it can be VERY important!

Let's say you are using a GridView and for each row, you are using a relation to the 'states' table to pull the name of a 'state' object:
<asp:BoundField DataField="item.state.state_name" HeaderText="State Name" SortExpression="item.state.state_name" />

Now this will work just fine, but each time you add a row, there is going to be another call to the DB to get the state name. Now if you only have a few rows, no big deal, but what if you are calling 100 records each with a a call to the state name and a country name? It is no longer one trip to get the items, it grows huge! Here is some ugly psuedo-code for understanding:
ONE_SQL_QUERY -> 100_ROWS_RETURNED (1 trip)
FOREACH(ROW row in 100_ROWS_RETURNED)
{
  QUERY_FOR_STATE_NAME (1 trip)
  QUERY_FOR_COUNTRY_NAME (1 trip)
}

That one query has now jumped to 201 database calls (1+100(1+1)) ... that is no small difference! Now, that first query, no matter how big and ugly it is is dwarfed by these petty little calls for one small string. Just imagine what happens if you are calling multiple and nested relations, that one call can easily jump to well over 1000 in no time.

So what to do?

While searching on Linq-to-Entities (yes, I still want to make the switch some time) I came across this: http://blogs.msdn.com/adonet/archive/2008/10/07/migrating-from-linq-to-sql-to-entity-framework-eager-loading.aspx which shows how you deal with this in both L2S and L2E (also answered my last question on my rant against L2E. This is actually a situation where (I think) L2E shines over L2S, but that's a whole other debate. Here is what you would need to do.
dbDataContext db = new dbDataContext();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<item>(i => i.state);
dlo.LoadWith<item>(i => i.country);
db.LoadOptions = dlo;

What this is doing is telling your DataContext to load those other elements with your every 'item' you load (don't forget to include using System.Data.Linq;). This will obviously make for a larger query to begin with, but it will cut out those multiple return trips that are the real problem!

On an somewhat related note... I am going to force myself to use L2E on my next project.

Why I decided to stick with Linq-to-SQL over Linq-to-Entities

I tried to make the switch... but Linq-to-Entities is just so much more work!

Recently I tried, I really tried to like L2E, but I just can't do it! It adds layer upon layers of extra crap I have to do over L2S while offering little to no improvements over the soon to be deprecated ORM. I am officially sticking with L2S until they get L2E better developed. Now I am hardly the first person to write about this, but I feel my concrete examples are somewhat helpful when trying to decide.

Now I have read a bunch of reason of why not to switch, but I stubbornly went along and tried it myself. I like to dumb down my reasons to simple examples of why I have come to loathe L2E (these are all using the same exact DB):

DataSources

I'll just show you on this one, it may not seem like a lot, but try it for yourself and see how confusing the syntax can get:

Linq-To-Entities

<asp:EntityDataSource ID="edsState" runat="server"
  ConnectionString="name=J1Entities"
  DefaultContainerName="J1Entities"
  EntitySetName="state" Select="it.[state_id], it.[state_name]">

Linq-to-SQL

<asp:LinqDataSource ID="ldsState" runat="server"
  ContextTypeName="dbDataContext"
  Select="new (state_id, state_name)" TableName="states" />

Now I don't know about you, but the L2S is just easier to understand. Why the hell do I have to use it.[column_name]? That doesn't make any sense. Not to mention the GUI may as well not be there if you are trying to use automatic Delete, Update or Insert.

Relations

This one is HUGE! Let's say you have a Foreign Key relation of state_id that relates to a table states and Primary Key that is also named state_id; here is how you do that in both frameworks:

Linq-To-Entities

item itm = new item() { item_name = "party" };
itm.state = db.state.First(s => s.state_id = 5));
db.AddToitem(itm);
db.SaveChanges();

Linq-to-SQL

item itm = new item() { item_name = "party", state_id = 5 };
db.items.InsertOnSubmit(itm);
db.SubmitChanges();

Now this is a super abbreviated case, but you can see the huge amount of overhead this can create not to mention programming time. I made these as succinct as I could not to try to advantage one over the other and the L2E is just burdensome. Even if I were to make a simple Get() method for state it would still make another call to the DB when I already know the relation integer!!! What an unnecessary step. And this extra work increases like crazy on large object with multiple relations.

Updating your Model

Now I can't relaly show this as an example, so I will just try to explain it. One big pain (I thought) in L2S was the fact that you had to pull your tables back in to your dbml every time you made a change to the DB (I know there are tools for this, but I am talking stock). L2E had this nifty feature that allowed you to 'refresh from the database' that I thought would actually work. The problem is, that if you change anything with any complexity whatsoever, like a Foreign Key, or even something as simple as changing a data type from VARCHAR to INT it seems to completely break your edmx. And this isn't simple to fix like a dbml, not even close. You can't drag a table into a edmx, and you can't seem to fix it through the refresh so what do you do?
  • Delete your edmx completely
  • Go into your web.config and delete the ConnectionString (unless you want duplicates)
  • Completely remake your edmx
  • Redo any changes in the crappy naming conventions to ALL of your tables that you changed (more on this later)
Wow... all that because I changed a relation? No thank you. Dragging that table into the dbml doesn't seem so bad now.

Relations Part 2

This one isn't as big of a deal, but it is annoying.

Linq-To-Entities

item itm = db.item.First(i => i.item_id == 12);
itm.stateReference.Load()
string whatState = itm.stateReference.Value.state_name;

Linq-to-SQL

item itm = db.items.First(i => i.item_id == 12);
string whatState = itm.state.state_name;

Just simpler in L2S and you don't have to remember the Load() if you forget, and it's in a lot of code, it can be extremely aggravating wondering why your relation is empty. Not to mention that reference.value syntax in there... more unnecessary (in my eyes) crap.

Relations Part 3

And this was the straw that broke the camels back... I was so used to the simplicity and convenience of L2S that this drove me nuts and was the reason I decided to give up. Relations are used so well with L2S that to show relations in something like a Repeater or GridView is almost trivially easy

Linq-To-Entities

Who knows?!

Linq-to-SQL

<asp:BoundField DataField="item.state.state_name" HeaderText="State Name" SortExpression="item.state.state_name" />

but since L2E requires that wonderful Load() method, it is now not so easy - to tell you the truth, I never figured it out how to make it work, probably have to call something on the RowDataBound event, but at this point I figured what is the point?

I am officially resigning from L2E (for now) and going back to L2S... maybe next go-round L2E won't be such a pain? Isn't new technology supposed to make our lives easier? Going from L2S to L2E is like 'upgrading' from a blender to a hand-mixer; it will get done, it will just take you longer and be more frustrating. Hopefully, considering that is the was MS is heading.

Shout it kick it on DotNetKicks.com

Getting started with Linq-To-Entities tutorial

The transition from Linq-to-SQL to .Net's Entities framework is incredibly simple

In my humble opinion, Linq is easily the greatest thing .Net has come out with in the past few years, and along with it, Linq-to-SQL (L2S) was a godsend. Being quick to hop on the L2S bandwagon, I quickly became a huge fan of the framework, it is amazing easy and useful. That being as it is, I was quite disappointed when I heard that Linq-to-SQL was no longer going to be advanced at all. Now, it is surely not dead, it is still very usable and effective at what it does, but I like to stay with frameworks that will be actively advanced and fbug-fixed. So I decided to make the jump to Linq-to-Entities (L2E), and it was suprisingly simple.

This guide should be a good starting point for anyone whether or not they are familiar with L2S, and a quick 'jumping guide' to L2S developers. Here's how to get started:

Make your ADO.NET Entity Data Model (.edmx file)

This is comparable to the .dbml file that you made with L2S.
  • Right click on your App_Code folder and select New Item
  • Select ADO.NET Entity Data Model and name it (I left the default Model.edmx for the example)
  • Choose Generate from database and click Next
  • Choose your database from the dropdown and choose the name to save the ConnectionString as and click Next
  • Choose all the things you want included, I just chose tables, but you may want to include views and SPs if you have them
  • *Be sure to remember the Model Namespace you choose for your .edmx (I will use DatabaseModel for the example) - click Finish

Now it will take a moment to run through and produce your .edmx; when it is done you will see a nice little representation of your tables reminiscent of the .dbml display in L2S:

Access your .edmx

Now we simply access the .edmx much like we did with L2S. You must remember to add Using DatabaseModel (or whatever your namespace was) to the code where you are using it. Now we will make a DatabaseModel object and use it to add a 'product' to the database. First, you need to make your Entity Access Object:
DatabaseEntities db = new DatabaseEntities();

Then you can use it to enter an object:
products p = new products();
p.product_name = "Word Processing Software";
p.price = 99;
p.stock = 100;
db.AddToproducts(p);
db.SaveChanges();

Once again, if you are familiar with L2S, this is almost the same exact thing! If you are new, this is very straight-forward and should be easy to understand:
  • Make an object of a type inside your database
  • Fill the object up with data
  • Put it into the database
  • Save the changes

Now L2E one-ups L2S and makes data entry even easier, this will accomplish the same as above:
db.AddToproducts(products.Createproducts(0, "Accounting Software", 300, 125));
db.SaveChanges();

Notice that the 'product_id' is entered as '0', that is because it is auto-incrementing, so it doesn't matter what I put there, it will be ignored. Now I don't know about you, but that little bit there will save me hundreds of lines of code! I am starting to like L2E already!

Display your data

Now that we have put a few objects in to the database, we can go and check it out. Later we will dig in with code, but first we will use a LinqDatasource/GridView:
  • Drag a LinqDataSource (LDS) on to the page and click Configure Data Source from the little square on the upper right of the LDS
  • Choose your Object Model and click Next
  • Choose your table and click Finished
  • Drag a GridView (GV) on to your page
  • In the GV option box, choose your datasource from the dropdown

now just view your page:

Modify Data

Now that we have seen how to put in data, we will modify some; once again, L2E makes it trivially simple:
products edit = (from p in db.products where p.product_id == 2 select p).First();
edit.product_name = "Account Software v2";
db.SaveChanges();

The Linq statement is EXACTLY like it would be in L2S, and just like in L2S, you can shorten up this with some Lambda integration; this will accomplish the same thing:
db.products.First(p => p.product_id == 2).product_name = "Accounting Software v2.1";
db.SaveChanges();

Deleting Items
Now that we have seen Insert and Update, the next logical step is Delete, and it is just as easy. Let us delete the 'Word Processing Software' frorm the table:
products del = (from p in db.products where p.product_id == 1 select p).First();
db.DeleteObject(del);
db.SaveChanges();

And the same exact thing shorthand with Lambdas:
db.DeleteObject(db.products.First(p => p.product_id == 1));
db.SaveChanges();

Once again, I have to say I like the approach that L2E takes of that of L2S, as it is not necessary to specify which table to delete from, as an object can only be deleted frorm the table that it is in.

Using your database relations

As in L2S, L2E takes great advantage of well designed databases and relations. If you noticed up above, the two tables in my database have a 1 to many relation frorm product to reviews. Each element in the 'reviews' table is required to relate to an element in the 'products' table (column 'product_id'). Let's fill the DB with a few more products and some reviews; a lot is going to go on here:
products p1 = db.products.First(p => p.product_id == 2);
products p2 = products.Createproducts(0, "Strategy Game", 49, 99);
products p3 = products.Createproducts(0, "Sports Game", 39, 99);
db.AddToproducts(p2);
db.AddToproducts(p3);

reviews r1_1 = reviews.Createreviews(0, "Much Improved", "this is a much better version", "Bill Brasky");
r1_1.productsReference.Value = p1;
reviews r2_1 = reviews.Createreviews(0, "Terrible", "worthless", "Dirk Digler");
r2_1.productsReference.Value = p2;
reviews r3_1 = reviews.Createreviews(0, "Great Game", "very tough AI", "Wonderboy");
r3_1.productsReference.Value = p3;
reviews r3_2 = reviews.Createreviews(0, "Very Fun", "the Bears rule", "Mike Ditka");
r3_2.productsReference.Value = p3;

db.AddToreviews(r1_1);
db.AddToreviews(r2_1);
db.AddToreviews(r3_1);
db.AddToreviews(r3_2);

db.SaveChanges();

Now to explain what just happened:
  • The first line gets an object of type products where product_id == 2 (this is 'Accounting Software v2.1')
  • The next two lines create new products
  • The next two submit those into the database

Now we have a total of 3 objects in the 'products' table and none in the 'review' table, that is where the next 8 lines come in. If you break up those 8 lines into pairs of two, you can see they are all the same thing really:
  • Make a new object of type reviews
  • Assign its foreign key reference to a products object

Since the database requires a relation between these two tables, you must be sure to set the reference. The last lines just submit everything and commit them to the database.

Now that that is in there, you can use the real power of relations in L2E. Once again, it is almost the same as L2S:
foreach (products p in (from _p in db.products select _p))
{
  Response.Write("<h3>" + p.product_name + " - $" + p.price + "</h3>");
  Response.Write("<b>Reviews:</b><div style='border:1px solid navy;padding:5px;'>");
  p.reviews.Load(); // this loads all the reviews that relate to the product p
  foreach (reviews r in p.reviews)
    Response.Write("<b>" + r.title + " - " + r.reviewer + "</b><br />" + r.review + "<br />");
  Response.Write("</div><br />");
}

And this is what you get:
Yes, I know I used the ugly 'Response.Write', and the output is hideous... but this is just a demo people! As you can see, it is incredibly easy to iterate through all of the reviews of a products, this big difference being that you need to call the .Load() method or they will not be populated. Now this is both a blessing and a curse; this is very efficient as it only loads the related objects if need be, but... it is soemthing that is easy to forget and may leave you scratching your head. I think I like it.

Now relations also work the other way as well. Say you have a reviews object, and you want to know the price of the products it is related to. No need to actually look up the products object, you just call the relation:
reviews r = db.reviews.First(_r => _r.review_id == 3);
r.productsReference.Load();
Response.Write("Price: $" + r.productsReference.Value.price.ToString());

Once again, notice that you have to Load() the reference, or you will get nothing.

Now that should get you started. Like I said, if you are familiar with L2S, this transition should be no problem, and if you are new to this whole Linq arena, this should be simple to pick up. This new ADO is getting more and more impressive the more MS works on it. I can't even imagine goging back to the old methods...

Searching for multiple strings using LINQ

text searching using LINQ

In the past I had covered Record Search with LINQ and that worked well, but did not cover text searching. Since you can't nest if-thens inside of a LINQ query it can be tough to get a useful text search.  Here is a method using a string array to search through a field for matches.

 

First thing is to take a TextBox and Split the Text to put it into a string array, but, it is not that simple. Since Linq can not make dynamic queries, we are going to have to run through an array with a set size. For this example I will choose 5, which will limit our search to only the first 5 terms. You could easily make this more, but this is a demo damn it. Once we decide that, we want to fill the array with String.Empty values as nulls will error out anything.

 

int wordLimit = 5;

string[] keywords = new string[wordLimit];

for (int i = 0; i < wordLimit; i++) keywords[i] = string.Empty;

string[] inputKeywords = txtTitle.Text.Split(new char[] { ' ', ',', '' }, wordLimit + 1, StringSplitOptions.RemoveEmptyEntries);

int max = inputKeywords.Length > wordLimit ? wordLimit : inputKeywords.Length;

for (int i = 0; i < max; i++)

      keywords[i] = inputKeywords[i];

 

This may seem like a lot, but this makes sure we get an array of exacly 5 elements that there are no empties in.  That way, if the user does not fill it in, *every* string contains a String.Empty, so it will work fine.  Next we just go and run through all the strings comparing it in this example to the 'title' property


return from p in someIQueryable where

             p.title.Contains(keywords[0]) &&

             p.title.Contains(keywords[1]) &&

             p.title.Contains(keywords[2]) &&

             p.title.Contains(keywords[3]) &&

             p.title.Contains(keywords[4])

     select p;


Right... now this isn't the most beautiful and elegant solution, but it works! it can be expanded to whatever size you want and will be accurate. It may seem like a lot of lines of code, but without dynamic LINQ query production, this is the only way I found it works stock. Simple and to the point!

GUI css writer: change your page style and rewrite your css on the fly

I have seen a lot of theme pickers out there, but not one that gives you 100% control like this

While working on a recent project, I had a complaint about the colors... I hate colors and I am no good at picking them, and when I do find some colors that I think are nice, apparently everyone else thinks they are crap.  This is why I truly respect web designers, as that is a true skill.  But to my chagrin I do not have a web designer, so I did the next best thing: get the blame off my back.  That's right, if you think you are so good with colors, why don't you pick them Mr. User?

 

So I went ahead and used the Farbtastic jQuery color picker Plug-in that I recently fell in love with, along with some nice Linq, XML, some good old .Net IO and a dash of clever css writing, and I was able to put together a (almost) fool-proof theme-designer with a friendly GUI.

 

First thing first, I needed both jQuery and the Farbtastic jQuery color picker Plug-in I just mentioned to be downloaded and called in my <head>.  Once I ran the Farbtastic demo to make sure everything was working fine, I set out on my adventure.

 

Next, I had to seperate my css into two distinct sheets, one: main_style.css would be jsut that, the main style; this never changes and is static.  Then, I made another stylesheet: theme.css that will hold my changing data and be re-written.  By using some clever techniques like not relying on any css border properties, I will be able to minimize the amount of code i have to re-write and be able to produce what looks like borders without actually using borders.  So instead of

/*theme.css*/
div.border { border: solid 5px Black; }

<!--html-->
<div class="border">some stuff with a border</div>

 

I will instead use something like this:

/*main_style.css*/
.border { padding:5px; }

/*theme.css*/
.border_color { background-color:Black; }
.content_color { background:White; }

<!--html-->
<div class="border border_color">
  <div class="inner_color">some stuff with a border</div>
</div>

 

These both produce what looks to be the exact same, the difference being that the second one does not actually have a border, it just looks like it with the padding and background color.  Now I know that the second one is more code, but that is the price you pay to have less code in your css and have your website be maintainable.  Once you write teh css writer one time, you will never have to touch the code again.  You can easily incorporate borders into the re-written css if you would like, that is just not the path I am going.

 

Now that that is understood, I can put in the color picker and the related TextBox controls to correspond to what colors I will be changing.  To do that, simply put this isnot your page where you want the picker: <div id="picker"></div> and then add your TextBox controls where you need them; each TextBox is the class 'colorwell' that allows them to be accessed easier by jQuery.  Once that is done, add a Button to submit everything.  So far, mine looks like the picture at the top of the post.  You will have to bind everything with jQuery now:

[code:js]

 <script type="text/javascript" charset="utf-8">
     $(document).ready(function() {
         var f = $.farbtastic('#picker');
         var p = $('#picker').css('opacity', 0.25);
         var selected;
         $('.colorwell')
      .each(function() { f.linkTo(this); $(this).css('opacity', 0.75); })
      .focus(function() {
          if (selected) {
              $(selected).css('opacity', 0.75).removeClass('colorwell-selected');
          }
          f.linkTo(this);
          p.css('opacity', 1);
          $(selected = this).css('opacity', 1).addClass('colorwell-selected');
      });
     });
 </script>

[/code]

 

Now comes the code-behind.  First thing I decided is that I am going to hold the data in an xml file; you could easily do this in a SQL database as well.  I named the file theme.xml and it looks like this:

<?xml version="1.0" encoding="utf-8"?>
<theme>
  <text>#444444</text>
  <borders>#4a4a4a</borders>
  <body>#ffffff</body>
  <links>#ff7700</links>
  <link_hover>#ffa500</link_hover>
  <button_text>#ffffff</button_text>
  <headers>#ffffff</headers>
  <background>#a53c3c</background>
</theme>

 

I populated it with my default color values of what will be changing.  Next, I made a class that will help me interact with the xml using Linq, I named this file xmlHelper.cs and placed it in my App_Code folder:

public static XElement getElement(XElement x, string element)
{ return (from e in x.Descendants(element) select e).First(); }

public static void writeElement(XElement x, string element, string value)
{
    XElement xe = getElement(x, element);
    xe.Value = value;
}

 

Now in my Page_Load I made a Dictionary<string, TextBox> and populated it with all of my TextBox controls and their corresponding xml Element name.  Now each time the page loads fresh, I simply load my values into the TextBox controls like this:

if (!IsPostBack)
{
    foreach (var v in colors)
        v.Value.Text = xmlHelper.getElement(x, v.Key).Value;
}

 

Now every time the page loads, it pulls the values out of the xml file and populates the TextBox controls.  Now that I have that, I need a way to write to the css file and the xml file.  A iadd that to my btnSubmit_Click event.  Once again, I can look through the Dictionary I had set up to this time use my xmlHelper class and write to the xml file.  That is done like this:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    foreach (var v in colors) xmlHelper.writeElement(x, v.Key, v.Value.Text);
    x.Save(themePath);
    //writeCss(); //this will be used in a moment
}

 

Now the xml is written, we need to write the css.  For this I just use the old IO and StringBuilder to write my css and overwrite the old file:

protected void writeCss()
{
    string cssThemeFile = Server.MapPath("~") + "\\css\\theme.css";
    StringBuilder sb = new StringBuilder();
    sb.Append("html{background-color:" + txtBg.Text + ";}");
    sb.Append(".border_color{ background-color:" + txtBorders.Text + ";}");
    sb.Append(".content_color{ background-color:" + txtBody.Text + ";}");
    sb.Append(".header_text{color:" + txtHeader.Text + ";}");
    sb.Append("a{color:" + txtLink.Text + ";}");
    sb.Append("a:hover{color:" + txtLinkHover.Text + ";}");
    sb.Append(".button:hover{background-color:" + txtLinkHover.Text + ";}");
    sb.Append(".button{background:" + txtLink.Text + ";color:" + txtButtonText.Text + ";}");
    File.Delete(cssThemeFile);
    TextWriter tw = new StreamWriter(cssThemeFile);
    tw.WriteLine(sb.ToString());
    tw.Close();
}

 

That will write my theme.css file, and that is that.  Now on every click, the values will be written to the xml file (this is unneccessary, but it loads the previous settings up for your user).  After that, the css file is re-written and immediately used by your page.  This does require that you have write permissions to whatever directory/directories you house your theme.css and theme.xml files in.  This also is easy to implement with each user profile having their own saved style (easier with SQL).  I hope this comes in handy for someone, I love it and have used it on a few projects now.

 

...And now they see it's not soo easy picking colors that actually look good :)

 

 

Record Search with LINQ: searching just got a lot easier!

A suprisingly simple search technique that will work for SQL tables, XML files, DataTables, etc.

Linq is just awesome.  I was recently asked a question on how to search through a data file with linq.  At first it seemed difficult, but after some though and fighting my way through the strange quirks, I realized how much easier Linq could [once again] make my life.

 

Here is how it's done (this article uses XML, but I have done the same exact thing with both SQL and DataTables, the concept is the same.)

 

The original question stirred from how to search through a bunch of recipes.  Each recipe (XML elements bolded) has a name, type (chicken, vegetable, etc.), calories that it contained, the amount of people that the recipe serves, and the instructions on how to make it.  Here is the dummy XML:


<?xml version="1.0" encoding="utf-8" ?>
<recipes>
    <recipe>
        <name>Hamburger</name>
        <serves>1</serves>
        <calories>500</calories>
        <type>Beef</type>
        <instructions>throw some crap together and cook it</instructions>
    </recipe>
    <recipe>
        <name>Chicken Nuggets</name>
        <serves>4</serves>
        <calories>400</calories>
        <type>Chicken</type>
        <instructions>throw some crap together and cook it</instructions>
    </recipe>
    <recipe>
        <name>Lasagna</name>
        <serves>8</serves>
        <calories>800</calories>
        <type>Pasta</type>
        <instructions>throw some crap together and cook it</instructions>
    </recipe>
    <recipe>
        <name>Ribeye</name>
        <serves>1</serves>
        <calories>600</calories>
        <type>Beef</type>
        <instructions>throw some crap together and cook it</instructions>
    </recipe>
    <recipe>
        <name>Drumsticks</name>
        <serves>3</serves>
        <calories>700</calories>
        <type>Chicken</type>
        <instructions>throw some crap together and cook it</instructions>
    </recipe>
    <recipe>
        <name>Beef Kabobs</name>
        <serves>4</serves>
        <calories>350</calories>
        <type>Beef</type>
        <instructions>throw some crap together and cook it</instructions>
    </recipe>
    <recipe>
        <name>Green Beans</name>
        <serves>5</serves>
        <calories>50</calories>
        <type>Vegetables</type>
        <instructions>throw some crap together and cook it</instructions>
    </recipe>
</recipes>

 

Now that we know our data structure, we can figure outhow to set up this search.  I am going to show a few different approaches so you can pick and choose which ones to use.  I am going to cover keyword search, number range search and specific text search.

 

First we have name search.  For that I am going to look for keywords.  I will allow users to enter none or as many as they want (to be more specific) into a TextBox.  These will be delimited with the standard space( ), comma(,) and semicolon(;).

 

Next there is type and serves which I am going to set to DropDownLists.  I am going to do this because not all numbers/words will be supported, I feel it is best to guide the user along with this and only supply available search possibilities, with 'any' always being the first option.

 

Finally there is the calories range.   For this, I will use two TextBoxes that will take in numbers of course; a minimum and a maximum.

 

Here is what the search interface looks like:

 

Here is the markup:

 

<fieldset>
    <legend>Search Recipes</legend>
    <ul>
        <li>
            <label for="name">Name</label>
            <asp:TextBox runat="server" ID="txtName" />
        </li>
        <li>
            <label for="type">Type</label>
            <asp:DropDownList ID="ddlType" runat="server" />
        </li>
       
        <li>
            <label for="serves">Serves:</label>
            <asp:DropDownList ID="ddlServes" runat="server" />
        </li>

        <li>
            <label for="calories">Calories</label>
            <asp:TextBox ID="txtCalMin" runat="server" Columns="3" />
            <span style="float:left"> to </span>
            <asp:TextBox ID="txtCalMax" runat="server" Columns="3" />
        </li>

        <li>
            <asp:Button ID="btnSearch" runat="server" Text="Search"
                onclick="btnSearch_Click" />
        </li>
    </ul>
</fieldset>

 

Now with that out of the way, we can start with the code.  First a global XElement x; and IEnumerable<XElement> filteredResults; has to be declared, x will be used within the program and initialized on Page_Load, filteredResults will be explained later.  After that is initialized, on a fresh page load (!IsPostBack)the DropDownLists must be populated:

 

protected void Page_Load(object sender, EventArgs e)
{
    x = XElement.Load(Server.MapPath(".") + "\\App_Data\\recipes.xml");
    if(!IsPostBack)
    {
        // get all of your different food 'types' and put them in the ddl
        ddlType.Items.Add("any");
        foreach(string s in ((from p in x.Descendants("type") orderby p.Value select p.Value).Distinct()))
            ddlType.Items.Add(s);
        ddlServes.Items.Add("any");
        foreach (string s in (from p in x.Descendants("serves") orderby p.Value select p.Value).Distinct())
            ddlServes.Items.Add(s);
    }
}

 

Now that the form is all ready,it is time to delve in to the actualy search.  Now keep in mind, the Linq syntax will be a bit differen, but the methods are teh EXACT same when you are working with another data type (SQL, DataTable, etc.)  After a lot of deliberation on how to do this, I decided to split it up in to two seperate parts.  First filter the elements using all the filters EXCEPT the keywords (name), then apply the keyword search.  The reason I am doing it this way is because that search could include zero terms, or 5,000; therefore iteration makes the least amount of work.  Also, for ease of use, the search will workregardless of what a user enters.  By default, the search will return everything, then narrow it down as users select more criteria.  For this I will make it so the default min/max calories are 0/9999 respectively (I will ignore any input that isn't integers) and make sure to ignore the type and  serves if 'any' is selected.  Here is the code for search, I will explain it afterwards:

 

protected void btnSearch_Click(object sender, EventArgs e)
{
    try
    {
        string[] searchTerms = txtName.Text.Split(new string[] {" ", ",", ";"}, StringSplitOptions.RemoveEmptyEntries); //gets all your search terms
        int calMin = 0;
        int calMax = 9999;
        try { calMin = int.Parse(txtCalMin.Text); }catch { }
        try { calMax = int.Parse(txtCalMax.Text); }catch { }
        if (calMin > calMax) Response.Write("<h2 style=\"color:red;\">Error: Calories<div style=\"font-size:.5em;\">Minimum can not be larger than maximum</div></h2>");
      
        var searchResults = from p in x.Descendants("recipe") // filters everything by all of the fields except Name
                        where
                            (ddlType.SelectedIndex == 0 ? true : p.Element("type").Value.Equals(ddlType.SelectedValue.ToString())) &&
                            (ddlServes.SelectedIndex == 0 ? true : p.Element("serves").Value.Equals(ddlServes.SelectedValue)) &&
                            (int.Parse(p.Element("calories").Value) >= calMin) &&
                            (int.Parse(p.Element("calories").Value) <= calMax)
                    select p;

        foreach (string s in searchTerms) // since name can be multiple words, this iterates through them all, making sure that all of the terms are present
            searchResults = from p in searchResults where p.Element("name").Value.ToLower().Contains(s.ToLower()) select p;

        if (searchResults.Count() > 0)
        {
            // output your results
            foreach (XElement xe in searchResults)
                pnlOutput.Controls.Add(new LiteralControl("<a href=\"#" + xe.Element("name").Value + "\"><div><h3>" + xe.Element("name").Value + "</h3> Calories: " + xe.Element("calories").Value + "</div></a>"));
        }
        else
            pnlOutput.Controls.Add(new LiteralControl("No Entries match your search criteria"));
    }
    catch (Exception ex)
    {
        pnlOutput.Controls.Add(new LiteralControl("<h3>Error</h3>"+ex.Message));
    }
    pnlOutput.Visible = true;
}

 

First off, you can see that the searchTerms are made by splitting the input into an array, pretty simple.  Next the min/max calories are set to defaults and attempted to be changed to the inputs, and will only be changed if there are valid inputs; it will output an error if minCal > maxCal.

 

The first leg of the search is pretty simple just return all descendants in the XML file of type recipe that follow the searhc criteria.  First I check the type, if the DropDownList is at SelectedIndex of 0 (which is the 'any' selection) I will return all, otherwise, jsut the ones that equal the selected type; I do the exact same for serves.  Then a simple check that returns those that calMin >= calories <=calMax.

 

That was not the part that confused me, it was how to get a dynamic number of search terms to be iterated through.  But this is where I cam up with the simplest of solutions.  Just search EACH term alone, and interate through it with Linq and a foreach... the beauty of the IEnumerable.  For each search term in searchTerms I simply run a new Linq statement that checks if the ameElement.Contains(that_search_term).  Therefore, every time this runs through, it will drop all entries that don't contain the term, each iteration [likely] returning fewer entries -- so simple!  But not really, for some reason (still not completely sure why), if I simply run:

 

foreach (string s in searchTerms)
    searchResults = from p in searchResults where p.Element("name").Value.ToLower().Contains(s.ToLower()) select p;

 

Does NOT work, it really only honors the last term; it is not overwriting searchResults every iteration.  After some testing, I had found out that I had to make a new variable (that's the filteredResult that we declared above) that would instead take place of searchResults and then we can write over it.  But also, you may not do this inside the foreach loop itself, as that still only honors the last term.  BUT, if the method is taken out and placed seperately, the overwrite seems to work.  But NOT for searchResults, I have to use filteredResults.  If anyone understnad exactly why this extra step is necessary, pleae enlighten me!  Here is how it is called:

 

filteredResults = searchResults;
foreach (string s in searchTerms) // since name can be multiple words, this iterates through them all, making sure that all of the terms are present
    filteredResults = iterateThroughSearchTerm(s);

 

And here is the method:

 

protected IEnumerable<XElement> iterateThroughSearchTerm(string term)
{
    return (from p in filteredResults where p.Element("name").Value.ToLower().Contains(term.ToLower()) select p);
}

 

All that is left is to simply output your findings, an error message or a 'sorry, nothing found' message.  And there you go, a bunch of different search approaches all covered at once.  Here is some example code to get you started.

 

 





 

XML to DataTable with LINQ

Easy way to get your XML into a DataTable

Now I might just be blind, or incredibly incapable at searching google or reading my LINQ books (very possible) but I hadn't found a simple way to get a 2-level XML document into a DataTable for use in a GridView or just simple DataTable manipulation utilizing LINQ (I assume this is because DTs are 2 dimensional, and XML files can be all sorts of mash-ups of information). Since LINQ is so powerful, I assumed it wouldn't be all that difficult, turns out I was right, it's pretty easy. Here is the code:

[code:c#]

public DataTable XElementToDataTable(XElement x)
{
  DataTable dt = new DataTable();

  XElement setup = (from p in x.Descendants() select p).First();
  foreach (XElement xe in setup.Descendants()) // build your DataTable
    dt.Columns.Add(new DataColumn(xe.Name.ToString(), typeof(string))); // add columns to your dt

  var all = from p in x.Descendants(setup.Name.ToString()) select p;
  foreach (XElement xe in all)
  {
    DataRow dr = dt.NewRow();
    foreach (XElement xe2 in xe.Descendants())
      dr[xe2.Name.ToString()] = xe2.Value; //add in the values
    dt.Rows.Add(dr);
  }
  return dt;
}

[/code]


This is completely dynamic, so it doesn't matter what or how many elements you have. It does rely on the first element to set the DataTable columns, so make sure that one is complete. Though the XML does have to be limited to 2-dimensional elements; in fact, I am not even sure what happens if you feed the function bad data? The XML should resemble this structure:

<?xml version="1.0" encoding="utf-8"?>
<root>
      <person>
            <age>26</age> 
            <name>stan</name> 
            <hobbies>partying</hobbies> 
      </person>
      <person>
            <age>26</age> 
            <name>matt</name> 
            <hobbies>being lame</hobbies> 
      </person>
</root>

In that structure, each person will be a row, and age, name and hobbies will the the columns in the datatable:
agenamehobbies
26 stan partying
26 matt being lame
Call it like this:

[code:c#]

// load your xml file (this one is named people and it is in my App_Data folder)
XElement x = XElement.Load(Server.MapPath(".") + "\\App_Data\\people.xml");//get your file
// declare a new DataTable and pass your XElement to it
DataTable dt = XElementToDataTable(x);

[/code]


And that's it, you have your DataTable.


Using DefaultIfEmpty() to check if an element exists with LINQ

This is a very handy way to test if an element exists using LINQ

Often you want to run something only if an element exists. There is always the try/catch method, but that doesn't seem very elegant to me, and this gave me an excuse to figure some more out about LINQ, which I am finding I like more and more.

This is actually very simple once you understand how DefaultIfEmpty() works. It may seem a bit obvious, but it returns a default value if something is not there. The easiest way to do this is to give it something explicitly so you know what it is returning for the default; I just use a dummy instance. For this example, I am using LINQ with XML and XElements

[code:c#]

//make your dummy element
XElement dummy = new XElement("dummy");
//assign it an easy to recognize null value
dummy.Value = "Does Not Exist";
//now run a query with it
foreach (XElement p in xmlFromFile.Elements("anElement").Descendants("someElement").DefaultIfEmpty(dummy))
{
  if(process.Value.Equals("Does Not Exist")
    //it does not exist
}

[/code]

It's just that easy. Works the exact same with SQL or any other data source as well.