March 2008 - Posts

LINQ to SQL maintains object relationships between all tables, mimicing how the database looks.  What I mean by that is that you can drill down (or up) through a database hierarchy in an object-oriented fashion, simply by referencing the property.  So an Employee class with a PK of Manager could reference that manager via:


Provided that relationship is not null.  Suppose there is a relationship that is null, which is State.  State is a reference table related to Employee, but sometimes the employee address isn't entered into the system when the employee name/ID is.  So, the state can be null.  In LINQ, State is represented by two properties:

State = the LINQ class reference to the state record, if not null.
StateKey = the key to the class record in the state table.

If in LINQ, you write code that says:

employee.StateKey = 1;

The StateKey is set to 1, but the State reference value is null until you call SubmitChanges() on the DataContext.  However, the other way around is not true; you can assign a state object like:

employee.State = this.Context.States.Single(i => i.StateKey == 1);

This assigns the State reference, but StateKey is also assigned to the key of the state object.

Posted by bmains | with no comments
Filed under:

After doing some forum posting around, I found this tool:  It seems to be pretty functional; I've messed around with it some, and it's going to help greatly in testing my LINQ queries.  Although there isn't any intellisense (at least that I've found), it's not too bad, and is great for a free download.  Try it out.

Posted by bmains | 1 comment(s)
Filed under:

LINQ maintains references to all of its relationships.  For instance, any PK or FK relationships are maintained as an object reference, so you can drill down into the PK or FK object or collection.  However, whenever you insert a new record, this reference is not populated (especially for PK's; haven't tested FK collections) whenever you call InsertOnSubmit, or add it to an entity set.  This reference is only populated whenever you submit the changes and the object is refreshed.

In a web application, it's not always best to submit changes after every insert, but for inserting new records, you have to do so to get the new key values and all.  For updates though, what you may want to consider is instead of assigning the key value, assign the reference value.  Let's say we have an employee class.  Employee references manager, a single entityref reference.  If the edit involves changing the manager, you can assign the managerkey, or the manager reference which is the object reference.  Instead of assigning the key value, you could assign the manager reference, and this updates the object to be the most up-to-date (manager key is updated with this approach too) and you don't have to call submitchanges to get this new reference.


Posted by bmains | 1 comment(s)
Filed under:

I've been asked about the different contexts that data can be queried, which there is some.  With LINQ to SQL, when querying against the data context, there is limited support for what can be done with LINQ to SQL; this is because the parser evaluates your LINQ query and converts it to a query that can be used to retrieve data from the database.  This is why you can see the SQL generated from a query for testing purposes.  This is why in LINQ to SQL queries, you can't use your own custom methods and and other methods used as well (such as string.IsNullOrEmpty()).  So when the data is queried as such:

var customers =
  from c in this.Context.Customers
  where c.IsActive == true
  select c;

This data is translated into a query and fetches the data against the database.  The queried data returns in the form of a queryable set of customers.  This customer has reference to all foreign keys in the system, so if there is a related orders table, the customer class has an Orders collection of type EntitySet.  This collection is loaded in a deferred fashion ( but could be loaded immediately (see the end of that article, the article on LoadWith method).

However, once you have a set of data brought down from the database, you can use regular LINQ queries that can incorporate custom methods and the like.  This is the LINQ to Object side of LINQ where LINQ isn't translating your query into a database call.  This would work like any system; when you get the data, it's converted into business objects for a meaningful representation of your data, and you can transform or use it to your liking.  So something like this:

var orders = customer.Orders.Where(i => i.OrderDate.Year > 2000);

You could replace the query with custom methods or objects and can use it in this context:

var orders = from o in customer.Orders
                  where this.MeetsCriteria(o.OrderStatus)
                  select o;

So that is the difference.

Posted by bmains | 1 comment(s)
Filed under:

I had a setup where I had an ajax tab panel, with a HeaderTemplate set with a label inside.  This label was there as a placeholder using an expression statement (<%$ %>).  What I found out though is that I had a label control inside the header template that, for whatever reason, still shows whenever you set TabPanel.Visible = false.  So, when setting the tab panel to false, it hides the nice tabbed image background, but keeps showing the label inside the tabs!

So, when changing the visibility of the tab, I also had to change the visibility of the inner header template label.  This seems weird, but hey whatever gets it to work is fine with me.


Posted by bmains | 1 comment(s)
Filed under:

When using LINQ, not everything works as you may expect.  Your custom methods in your code, for instance, do not work at all in a LINQ query.  When the LINQ expressions are built, objects in LINQ have to map over to a valid C# or VB.NET operator so that it can be translated to a database call.  So not all the commands in .NET may work in a LINQ query; I don't have a verified list.

Posted by bmains | with no comments
Filed under:

One of the bigger challenges comes from errors that occur due to inserts, updates, or deletes to the database.  This can be not so much from the LINQ side as an issue, but from the database side.  For instance, with LINQ to SQL, a LINQ object has a string as a property.  This property maps to a database column defined as nvarchar(50).  However, LINQ to SQL designer does not embed any length checking into the business object, so you can easily assign the property a string 60 characters long, which will blow up when it's passed to the SQL database.  The resulting error won't give you much information, such as the table/column this occurred for.  And if you have a lot of updates, this will be hard to figure out what went wrong.

The approach I've used to counteract this is to use reflection to access the ColumnAttribute class in the System.Data.Linq.Mapping namespace.  This attribute defines the database column name, data type, precision, scale, etc. that a column can be.  Using this information and with some added logic to take this information and translate it into validation rules, I could easily add more detailed error information that pinpointed the problem immediately.

The solution is to loop through the object's properties:

Type linqType = linqObject.GetType();
PropertyInfo[] linqProperties = linqType.GetProperties(); // all are public properties

foreach (PropertyInfo property in linqProperties)
    //Logic here

The ColumnAttribute can be retrieved through the use of property.GetCustomAttributes(typeof(ColumnAttribute), true).  This method returns an array of attributes, as some attributes can be used multiple times.  But ColumnAttribute is defined once, so accessing the zero index is good.  However, do it this way:

object[] attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
if (attributes.Length > 0)
   //Process column

The DbType property contains a string like "DateTime", "DateTime Not Null", "NVarChar(50)", "int not null", etc.  Using this, the precision, scale, and data type can be parsed, using some extra logic to determine what precision and scale are, to make sure text isn't passed a specific length and numbers are within the correct range.  In addition, the not null text is placed in DbType, but there is a CanBeNull property that determines nullability.

The common problems are not null dates that haven't been assigned are values like "1/1/0001", and SQL's datetime requires a minimum of 1/1/1753.  Another issue is strings going beyond the length.  Numeric values with a precise precision and scale (like numeric(4,2)) translates to a decimal, so it's possible that value will be out of range.

Posted by bmains | with no comments
Filed under:

As a general recommendation, I would pass in object references to DAL, BAL, or other methods in your application, rather than passing in individual properties.  The reason is you can conceal the properties you need in your code.  For instance, look at this method:

public OrderCollection GetOrders(int customerKey) { }

Now, suppose you change the property you use to query customers from an integer to a guid.  This requires an interface change to the BAL or DAL object.  As an alternative, if you provide this:

public OrderCollection GetOrders(Customer customer) { }

The field used to get orders isn't directly exposed and can be changed on the fly.

Normally, customer key as an integer will never change; however, it reduces one explicit dependency in your code and all the maintenance associated with interface changes.

Posted by bmains | 1 comment(s)
Filed under:

If you like architecture, you may realize there are some challenges that come into play when you try to bind data to the interface controls in ASP.NET.  Because data is often normalized, and this data is structured in several parent-child relationships, normalizing these for a tablular control like a grid view can be a big challenge.  Often, the approach is to use template fields, such as shown below:

<asp:GridView ..>
    <asp:TemplateField HeaderText="Child">
        <asp:Label id="lblChildField" runat="server" Text='<%# $((ChildObject)Eval("Child")).ChildProperty %>' />

Essentially, if object Parent has a property Child, this property is of type ChildObject.  Now, ChildProperty is a property of the ChildObject type, which is stored in the Child property of the class being bound in this example.  I hope I was able to explain that in a way that makes sense. Big Smile

So, is there an easier way?  Could there be a way to flatten a complex hierarchy more easily?  Certainly, a collection could be converted to a data table, or another business object can be used to contain all the important information necessary to display the important information in the grid.  I found a variant of option 2 that is useful, in case you are interested.

One of the new features of the .NET 3.0-3.5 frameworks is anonymous types.  For instance, I can define the following:

var person = new { Name = "Brian", Age=30 };

And this creates a new object with the signature of the name and age properties.  For more information on anonymous types, check these out:

So, suppose you had this schema:


Customer.Orders represents:


To normalize this, you can use the following approach:

ArrayList list = new ArrayList();

foreach (Customer customer in this.Context.Customers)
  foreach (Order order in customer.Orders)
    var item = new
      CustomerID = customer.CustomerID,
      CustomerName = customer.CustomerName,
      OrderDate = order.OrderDate,
      OrderTotal = order.OrderTotal


Notice the anonymous declaration, which creates an anonymous type of CustomerID, CustomerName, OrderDate, and OrderTotal.  This anonymous type is added to an array list; all that's needed is an enumerable list to be bound to the grid as such:

this.GridView1.DataSource = list;

Simply have the grid use the anonymous type properties in the list of bound field columns, and you're good to go.  Although this is more work, this is one situation; in some situations, you could simply query and join customers and orders, returning an anonymous type from a LINQ query, and using this to bind to the grid.

Posted by bmains | 2 comment(s)
Filed under:

It seems like this is a very popular message with SQL Server 2005 Express (if you google it, you'll find a lot of resources), and is often a hard one to figure out.  However, there are some ways that you can get to the bottom of it.  For instance, whenever SQL Server 2005 express tries to attach the database, a log is generated stating the process that is occurring.  You can get to this log file by going to this folder (note: replace <User> with your user name):

XP: C:\Documents and Settings\<User>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
VISTA: C:\Users\Brian\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

At any rate, check out the error.log, and see the names of the databases that were erroring.  For me, it happened because the SQL Server Developer instance didn't have certain databases in the MSSQL.1\MSSQL\Template Data folder, but had them in the \Data folder.  Simply copying them over solved the problem for me.

Another problem you may experience is that the SQLServer2005$MSSqlUser$<Server Name>$SQLEXPRESS (replace <Server Name> with the name of your server) did not have permissions to the folder listed above; I added it in case of permissions issues.  This may not be the issue, but take note that you may need to grant access for this account in some way.

I hope this helps someone solve this problem.

Posted by bmains | with no comments
Filed under:

When using reflection, you may get an error back that doesn't seem to fit the mold of reflection; I was dynamically executing an object through reflection, and I got the error "object not set to an instance of an object".  The class I was trying to access had an internal constructor with some code in it.  That code was failing, as there was a null reference, and therefore a null reference came back.

But it would appear there was an issue with the constructor call when executed (line 3) below.  It just turned out there was a deeper issue.

ConstructorInfo ctor = typeof(T).GetConstructor(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic, null, System.Type.EmptyTypes, null);
if (ctor != null)
return (T)ctor.Invoke(new object[] { });
return default(T);

Posted by bmains | with no comments
Filed under: