ScrewTurn Wiki is a Great Open-Source Tool

Easy setup and a breeze to use

Recently, the nearly complete lack of documentation for my open-source ticket system Slick-Ticket was brought to my attention, so I decided to document it. In the sprit of open-source, I figured why not do it in a wiki format and let my userbase contribute if they wanted to.

So I did some searching and came across ScrewTurn Wiki which is a fantastic open-source Asp.Net wiki, I highly recommend it. I thought about doing a tutorial, but the setup was so stinkin' easy, I figured there was really no point - just change one variable in the web.config and you are running; big props to the guys over there for making it so easy.

So, here is my documentation for Slick-Ticket if anyone wants to see how ScrewTurn looks.

Writing Dynamic Linq Queries in Linq-to-Entities

Believe it or not, L2E actually shines in this area

I am stubbornly plugging along with L2E, and I am even becoming kind of fond of it (in some areas). For example, I really like the way it handles dynamic queries, even more than L2E because you can pass in objects, not just strings! The syntax is a little different, but I think it is easier to understand and a better approach.

setup

First thing is first: you have to use the System.Linq.Dynamic.dll just like with Linq-to_SQL, you can get it from the links on ScottGu's blog. Then just make sure to throw it in your bin folder (or reference it) and include this in the program:
using System.Linq.Dynamic;

how to use 'where'

Where() is a bit different here. In L2S it just takes in a string, but here I will focus on the following overload:
(extension)IQueryable<T> IQueryable<T>
  .Where(string predicate, params object[] values)

Notice it does take in a string, but also the object array. So say I want to recreate this query:
var results = db.branch
  .Where(b => b.branch_id == 5 || b.display == "Hello");

With a dynamic query I would do this:
var results = db.branch
  .Where("branch_id == @0 || display == @1", 
    new object[] {5, "Hello"});

See how easy that was? The @0 and @1 are simply replaced with the corresponding objects in the array. Also notice that the array takes in all sorts of different object types as it does not matter what you are putting through. Now obviously you aren't going to set the array manually every time, that would defeat the purpose of the dynamic query, but you can easily see how this works.

In some programs, I have even built the string part (myQuery in this example) of the query dynamically with a running count so I know what @ to place in the string; along with the string being built I would add the objects to a List<object> myObjects and simply call the final products with:
var results = db.branch
  .Where(myQuery, myObjects.ToArray());

how to use 'select'

This is identical to L2S, very simple. If you don't use a Select(), it will just grab all the full objects. But if you want to grab select parts, or change names of some stuff, it is not difficult. A simple dynamic Select could look like this:
var results = db.branch
  .Select("new(branch_id,display)");

That would just grab the branch_id and display values of the branch items. But you can also change the name of the column they are represented in which can often be useful in dynamically produced tables and such:
var results = db.branch
  .Select("new(branch_id as BRANCH,display as NINJAS)");

Those columns will now be titled 'BRANCH' and 'NINJAS' respectively. Not too tough there. I think L2E got this dynamic query thing pretty spot on.

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...

Installing ELMAH for simple error logging

This is easily the simplest error logging method I have ever used - it is fantastic!

Error logging is one of the necessary evils of programming. No matter how good you think your program is, it is going to have errors (even with my code, which is flawless... ha). If you are not logging errors some way, you should be, right now!

ELMAH is an error logging framework for Asp.Net and it literally could not be easier and more convenient. You can go from reading this post to having ELMAH logging your errors, publishing RSS feeds and all sorts of other crap in a matter of 5 minutes. This tutorial deals with SQL Server, but you can also store your errors in many other ways such as XML, memory, Oracles, etc. - most of them are well documented... did I say this tool is awesome yet?

get what you need

Go to the download page and download whatever version you want; I am using 1.0 BETA 3. Then simply copy the (the proper one based on your .Net version) Elmah.dll to your bin folder; that's really all you need to do. Alternatively, if you you double-click on demo.cmd it will lauch a demo with Cassinni - I was very impressed with this little added functionality!

make your error logging table is SQL

Pretty much a no brainer here: there is a file called SQLServer.sql in the db folder you just downloaded; run it on the database you want to store the errors in. I used an existing DB so I could just reuse permissions and connectionStrings.

set up your web.config

Just a few changes to your web.config, and you will be up and running. First, add this into your <configSections>:

<sectionGroup name="elmah">
  <section name="security" requirePermission="false"
    type="Elmah.SecuritySectionHandler, Elmah" />
  <section name="errorLog" requirePermission="false"
    type="Elmah.ErrorLogSectionHandler, Elmah" />
  <section name="errorMail" requirePermission="false"
    type="Elmah.ErrorMailSectionHandler, Elmah" />
  <section name="errorFilter" requirePermission="false"
    type="Elmah.ErrorFilterSectionHandler, Elmah" />
</sectionGroup


Next, add this new section directly below your <configSections>:

<elmah>
  <security allowRemoteAccess="yes" />
  <errorLog type="Elmah.SqlErrorLog, Elmah"
    connectionStringName="some_con_string" />
</elmah>


Be sure to set this up with the proper connectionString. Note that I allow remote access, be sure to change this if you don't want to. Now go into the <httpHandlers> section and add this:

<add verb="POST,GET,HEAD" path="elmah.axd"
  type="Elmah.ErrorLogPageFactory, Elmah" />


And the last thing you need to do is add this to your <httpModules> section:

<add name="ErrorLog" type="Elmah.ErrorLogModule, Elmah" />


you are now logging errors

No need to do anything else. Now just navigate to ~/elmah.axd and you will see your error log. All unhandled errors will be logged, no matter what your customErrors is set to! Also, if you want to log handled errors you just grab them with this:
ErrorSignal.FromCurrentContext().Raise(ex);

Where ex is your Exception.

I can't give enough praise for this tool, it is just awesome! Much better than my crappy home-brewed error logging and so much more easy to use.

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!

Getting Foreign Key Values with Linq-to-Entities

A little more complicated that just calling the property...

Once again, L2E makes life a bit tougher. Let us say that we have a database record with a foreign key 'state_id' that links to a 'states' table. In this said record, we want to get the integer value of that 'state_id' column (which is the unique identifier/primary key of the states table). BUT, we don't want to pull the state information, we just want the indentifier. Once again, in L2S, this was trivial:

linq-to-sql
var a_record = 
  dataContext.records.First(r => r.record_id == 1);
int s_id = a_record.state_id;

Doesn't get much simpler than that, and this makes perfect sense. But L2E does not allow this... why I do not know, but you can't simply call on the property 'state_id' because it is not there.

linq-to-entities
// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state_id; // doesn't exist

This will not work either:

linq-to-entities
// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state.state_id; // not populated

Instead, the following *will* work, but it pulls the 'state' and that is an extra pull we don't need:

linq-to-entities
// this works
var a_record = 
  entities.Include("state").records
  .First(r => r.record_id == 1);
int s_id = record.state.state_id; // populated this time

Granted this example is small, but what if that was a table with a giant-sized record?

linq-to-entities
// the proper way
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = Convert.ToInt32(record.stateReference
  .EntityKey.EntityKeyValues.First().Value.ToString());

Wow, that is a lot of code to get a simple integer. This is so aggravating to me because that integer is clearly stored in the DB, but there is no clean way to get it, sp insteada you have to go through the references EntityKey.

Instead, I often write a static extension so I can simply call some_record.stated_id() to get the integer:

public static int isff_tracker_id(this state s)
{ 
  return Convert.ToInt32(record.stateReference.EntityKey
    .EntityKeyValues.First().Value.ToString()); 
}

Now, I am not L2E master, but if someone can point out a better way to do this, I would be greatful! This forced learning of L2E reminds me daily why I will stick with L2S next time.

Slick-Ticket hits 1000 downloads!

My open source trouble ticket system has hit 1000 downloads in less than 4 months

I suppose this is more of a bragging point than anything, but I am pretty proud.  I released Slick-Ticket on Jan 18 and it currently has over 1000 downloads on CodePlex and is going quite strong.  I have had a lot of great input and the mini-community has really helped me track down bugs and improve the software. 

 

I have a few reviews/ratings and they are all good so far, so that is encouraging.  Here is one that made my day:

 

We are actually up and running for 2 weeks live supporting over 700 users and currently over 220 users enrolled with profiles.  Over 300 tickets in just two weeks and our users love it.  Works almost flawlessly but I do have a few quick questions.

 

Well, hopefully I can make that *almost flawlessy* into a *flawlessly* (I fixed the bugs he reported).  Hooray for open-source!

Javascript replaceAll function

javascript .replace() only replaces on instance of a character/string, this will replace them all (with no looping)

This may be well know, but I did not realize this until it broke a js function where I was converting currency to integers; anything over $999,999 would not work right, apparently because the extra comma when you hit $1,000,000 was not getting replaced.

At first I went through and looped though a string, but that turned out to be ugly, and likely not as efficient as it could be. So I rooted around with some regexs and figured out the following, turns out it is clean and simple:

function replaceAll(txt, replace, with_this) {
  return txt.replace(new RegExp(replace, 'g'),with_this);
}

So now, replaceAll('1,000,000', ',', '') will return '1000000' and *not* '1000,000'.

Getting a Repeater/GridView to show child values of relationships with Entities

Once again, it is a bit more involved than L2S, but relatively simple

I have made L2S my go-to for a while now, and I will keep referring back to it as a baseline of how ORMs *should* work. When I am using a repeater, and I want to show a related object value, I can simply do this with L2S (same syntax for L2E as well):
<%# Eval("state.state_name") %>

Where 'state' is a related object. Similarly, if I try the same thing in L2E, it will not error out, it will just show up blank (I would much prefer an error). Like I said, the syntax is the exact same for that part, it is just different when you are getting your data. You now need to explicitly include what child objects you want to load with your data. So, for the above example, I could something like this:

linq-to-sql
var results = from p in db.records select p;
Repeater1.DataSource = results;
Repeater1.DataBind();

But, to get anything other than blanks to show up with L2E, I need to change it a bit:

linq-to-entities
var results = from p in db.records.Include("state") 
  select p;
Repeater1.DataSource = results;
Repeater1.DataBind();

Now it knows that 'state' has to be loaded with the original records. You can chain mulitple Include()s along to include more and more stuff as well:

linq-to-entities
var results = from p in 
  db.records.Include("state").Include("city")
  select p;

Populating Entities Foreign Key Relationships with EntityKey (and no extra trip to the db)

Relationships in Entities as not as obvious as they are in Linq-to-SQL

Despite my reluctance to step into the darkside of L2E, I decided that my current project I am forcing myself to use it. It is bound to be (as it has already proven) frustrating and painful, but I think it's a good idea to learn.

First problem I have run into is inserting records with relationships. In L2S, it is very simple and the syntax could not be easier. If I have a record, and it has a relation to a table of cities, I could simply make a new 'record' and set the 'city_id' to the integer primary key of the city table:

linq-to-sql
record r = new record() { city_id = 5 };

But, with L2E, it is not that simple. In the past, I had shown how to relate a record using a full trip to the server to get the entire entity; but that is not efficient and/or the right way to do it. Now why we can't do something more similar to above will hopefully be covered in the next release, but here is as close as I can seem to get the syntax:

linq-to-entities
record r = new record();
r.cityReference.EntityKey = new EntityKey(
  "YourEntitiesNamespace.cities", "city_id", 5);

Now, this is not nearly as clean as the L2S, but it does not require a trip to the db and isn't too bad. You will notice that the record has a property 'cityReference' - this is automatically made by the edmx because there is a foreign key reference in the table from record->cities. Then the new EntityKey just takes in 3 parameters (it is overloaded with other ways to input as well):
  • string qualifiedEntitySetName which will be your entityNameSpace.TableName
  • string keyName your primary key field
  • object keyValue the value in the primary key field