Eager Loading more than 1 table in LinqtoSql

When I’ve tried in Linq2Sql to load table with 2 child tables, I’ve noticed, that multiple SQLs are generated. I’ve found that  it is
a known issue, if you try to specify more than one to pre-load it just  picks which one to pre-load and which others to leave deferred (simply ignoring those LoadWith hints)
There are more explanations in 
The reason the relationship in your blog post above is generating multiple queries is that you have two (1:n) relationship (Customers->Orders) and (Orders->OrderDetails). If you just had one (1:n) relationship (Customer->Orders) or (Orders->OrderDetails) LINQ to SQL would optimize and grab it in one query (using a JOIN). 

The alternative -to use SQL and POCO classes-see http://stackoverflow.com/questions/238504/linq-to-sql-loading-child-entities-without-using-dataloadoptions?rq=1

Fortunately the problem is not applicable to Entity Framework, that we want to use in future development instead of Linq2Sql
Product firstProduct =  db.Product.Include("OrderDetail").Include("Supplier").First();

м

Advertisements

Convert collections of enums to collection of strings and vice versa

Recently I needed to convert collections of  strings, that represent enum names, to collection of enums, and opposite,  to convert collections of   enums  to collection of  
strings. I didn’t find standard LINQ extensions.
However, in our big collection of helper extensions I found what I needed – just with different names:
/// <summary>
/// Safe conversion, ignore any unexpected strings

/// Consider to name as Convert extension
/// </summary>
/// <typeparam name=”EnumType”></typeparam>
/// <param name=”stringsList”></param>
/// <returns></returns>
public static
List<EnumType> StringsListAsEnumList<EnumType>(this List<string> stringsList) where EnumType : struct, IComparable, IConvertible, IFormattable
    {
List<EnumType> enumsList = new List<EnumType>();
foreach (string sProvider in stringsList)
    {
    EnumType provider;
    if (
EnumHelper.TryParse<EnumType>(sProvider, out provider))
    {
    enumsList.Add(provider);
    }
    }
    return enumsList;
    }


/// <summary>
/// Convert each element of collection to string
/// </summary>
/// <typeparam name=”T”></typeparam>
/// <param name=”objects”></param>
/// <returns></returns>
public static
IEnumerable<string> ToStrings<T>(this IEnumerable<T> objects)
{//from http://www.c-sharpcorner.com/Blogs/997/using-linq-to-convert-an-array-from-one-type-to-another.aspx
return objects.Select(en => en.ToString());
}

Specify Linq To SQL ConnectionString explicitly

When modifying Linq to  Sql data model in Visual Studio 2010,  it re-assigns ConnectionString that is available on developer’s machine.
Because the name can be different on different machines, Designer often replace it with something like ConnectionString1, which causes errors during deployment.
It requires developers to ensure that ConnectionString stays unchanged.
 
More reliable way is to use context constructor with explicit ConnectionString name instead of parameterless default constructor

GOOD:
  var ctx = new MyModelDataContext(Settings.Default.ConnectionString);
Not good:
         var ctx = new MyModelDataContext();

Select First Row In Group using Custom Comparer

We have a requirement for a list of itineraries with multple itinerary items show only single itinerary in the list with details of one of the items  selected based on some custom sorting logic. So I needed to group by itinerary ID and sort by custom Comparison and select the first in each group.

 

Below is a LinqPad file that I’ve used for testing:
 

void Main()
{
// http://smehrozalam.wordpress.com/2009/12/29/linq-how-to-get-the-latest-last-record-with-a-group-by-clause/
// http://stackoverflow.com/questions/6963707/linq-query-group-by-and-selecting-first-items/7456167#7456167
var rows=new List<MyRow>()
{
new MyRow(1,ProductType.F,new DateTime(2012,01,01),“A”)
,new MyRow( 1,ProductType.C, new DateTime(2012,01,01),“B”)
,new MyRow(1,ProductType.H, new DateTime(2012,01,01),“C”)
,new MyRow(2,ProductType.C, new DateTime(2012,01,01),“D”)
,new MyRow(2,ProductType.Hnew DateTime(2012,01,01),“E”)
,new MyRow(3,ProductType.F, new DateTime(2012,02,01),“F”)
,new MyRow(3,ProductType.F, new DateTime(2012,03,01),“G”)
,new MyRow(3,ProductType.F, new DateTime(2012,01,05),“H”)
,new MyRow(3,ProductType.H, new DateTime(2011,01,01),“I”)
,new MyRow(4,ProductType.C, new DateTime(2012,01,01),“J”)
,new MyRow(5,ProductType.H, new DateTime(2012,01,01),“K”)
,new MyRow(6,ProductType.C, new DateTime(2012,01,01),“L”)
,new MyRow(6,ProductType.H, new DateTime(2011,01,01),“M”)

} ;

var firstsInGroups= from p in rows
//where conditions or joins with other tables to be included here
     group p by p.ID into grp
select grp.First();

firstsInGroups.Dump();

var firstsByCompareInGroups= from p in rows
group p by p.ID into grp
select grp.OrderBy(a => a, new CompareRows()).First();
firstsByCompareInGroups.Dump();

}

// Define other methods and classes here
public class  MyRow
{ public int ID;
public ProductType Type;
  public DateTime StartTime;
public string OtherData;

  public MyRow( int id,  ProductType type,   DateTime startTime,   string otherData)
{
ID=id;
   Type=type;
   StartTime=startTime;
  OtherData= otherData;
}
  }
public enum ProductType
  {F,C,H}

// http://msdn.microsoft.com/en-us/library/bb549422.aspx
  public class CompareRows : IComparer<MyRow>
{
// Because the class implements IComparer, it must define a
// Compare method. The method returns a signed integer that indicates
// whether s1 > s2 (return is greater than 0), s1 < s2 (return is negative),
// or s1 equals s2 (return value is 0). This Compare method compares strings.
public int Compare(MyRow r1, MyRow r2)
{
if(r1.Type==r2.Type)
{
return DateTime.Compare(r1.StartTime, r2.StartTime);
}
//F is the best type
else if (r1.Type==ProductType.F)
{
   return 1;
}
else if (r2.Type==ProductType.F)
{
   return 1;
}
else //for C and H min Date is better
{ int compareDates=DateTime.Compare(r1.StartTime.Date, r2.StartTime.Date);
   if(compareDates!=0)
{
return compareDates;
}
else//For the same date C is better
{
if (r1.Type==ProductType.C)
{
return 1;
}
else if (r2.Type==ProductType.C)
{
return 1;
}
Debug.Assert(false,“should not be here”);
return 0;
}
}
}
}

 

Include MoreLinq Library for LINQ extensions

Last week I needed to find an object from collection with maximum value of the property and wrote the following

double maxHours = (from loc in locations select loc.TotalHours).Max();
var location = (from loc in locations where (loc.TotalHours == maxHours) select loc).FirstOrDefault();
but wasn’t happy and checked the internet.
The answer in http://stackoverflow.com/questions/914109/how-to-use-linq-to-select-object-with-minimum-or-maximum-property-value/914198#914198  referred to
http://code.google.com/p/morelinq/
I’ve  downloaded the DLL and  tried to reference ‘MoreLinq’  from signed DLL,but got Error    609    Assembly generation failed — Referenced assembly ‘MoreLinq’ does not have a strong name.
The issue 30 Make the assembly strong named http://code.google.com/p/morelinq/issues/detail?id=30&can=1 marked as fixed in 2010, but downloads are still old 2009

I decided to download  source code, but I do not have experience with SVN and instructions on  http://code.google.com/p/morelinq/source/checkout  were confusing for me.
It seems that  In SVN “anonymously check out” has a meaning “Get Latest” in TFS/SourceSafe terms.
Fortunately I was able to install http://www.sliksvn.com/en/download and created a batch to get source, which includes certificate key

GetSRCMoreLinq.BAT:
@rem from http://code.google.com/p/morelinq/source/checkout
@rem Use this command to anonymously check out the latest project source code:
cd C:Program FilesSlikSvnbin
@rem # Non-members may check out a read-only working copy anonymously over HTTP.
svn checkout http://morelinq.googlecode.com/svn/trunk/ C:MoreLinqSrc
@pause

Use CompiledQuery.Compile to improve LINQ to SQL performance

 

 

After reading DLinq (Linq to SQL) Performance and in particular
Part 4  I had a few questions.

If CompiledQuery.Compile gives so much benefits, why not to do it for all Linq To Sql queries? Is any essential disadvantages of compiling all select queries? What are conditions, when compiling makes whose performance, for how much percentage? World be good to have default on application config level or on DBML level to specify are all select queries to be compiled? And the same questions about Entity Framework CompiledQuery Class.

However in comments I’ve found answer  of the author ricom

6 Jul 2007 3:08 AM
Compiling the query makes it durable. There is no need for this, nor is there any desire, unless you intend to run that same query many times.
SQL provides regular select statements, prepared select statements, and stored procedures for a reason.  Linq now has analogs.

Also from 10 Tips to Improve your LINQ to SQL Application Performance
 

If you are using CompiledQuery make sure that you are using it more than once as it is more costly than normal querying for the first time. The resulting function coming as a CompiledQuery is an object, having the SQL statement and the delegate to apply it.  And your delegate has the ability to replace the variables (or parameters) in the resulting query.

However I feel that many developers are not informed enough about benefits of Compile.
I think that tools like FxCop and Resharper should check the queries  and suggest if compiling is recommended.

Related Articles for LINQ to SQL:
MSDN How to: Store and Reuse Queries (LINQ to SQL)
10 Tips to Improve your LINQ to SQL Application Performance

Related Articles for Entity Framework:
MSDN: CompiledQuery Class
Exploring the Performance of the ADO.NET Entity Framework – Part 1

Exploring the Performance of the ADO.NET Entity Framework – Part 2
ADO.NET Entity Framework 4.0: Making it fast through Compiled Query
 

Lookup Tables with fallback support

Our database has a few lookup tables that uses similar approach to implemented by .Net Framework for localized resources:

At run time, ASP.NET uses the resource file that is the best match for the setting of the CurrentUICulture property. If there is no match for the current UI culture, ASP.NET uses resource fallback. It starts by searching for resources for a specific culture. If those are not available, it searches for the resources for a neutral culture. If these are not found, ASP.NET loads the default resource file.

Let’s assume that we have a look-up table with Time Zones for different countries, states within the countries and post codes within state.
To reduce maintenance of lookup table, if most of the country has one timezone, but only a few states(or post codes) have different timezones, we are not storing information about all states/postcodes, but storing default timezone for the country and then exceptional  states/postcodes:

Country Code State Code Post Code Time Zone Comment(not a part of database table)
CC1 Null Null 1 Simple -the same Time Zone for the whole country
CC2 ST1 Null 2 In the country different states have different zones
CC2 ST2 Null 3 regardless of postcodes
CC3 ST1 1001 2 In the same state different post codes have different time zones
CC3 ST1 1002 3
CC4 null 1001 2 Country doesn’t have states, but different post codes have different time zones
CC4 null 1002 3
CC5 null null 2 Most of states in the country have one time zone, but some states have different time zones
CC5 ST55 null 3
CC6 ST5 null 2 Most of postcodes in the state have one time zone, but some postcodes have different time zones
CC6 ST5 2007 3

The algorithm to retrieve the the timezone is the following
1. Try to retrieve the row for specified country, state and postcode. If record found, we return the time zone.
2.  If record not found, try to retrieve the row for specified country and state. If record found, we return the time zone.
3.  If record not found, try to retrieve the row for specified country. If record found, we return the time zone.
4.  If record not found, throw exception- we expect that at least 1 record per country should exist.

This approach works quite well, but I have some  concerns about performance. Usually the table is stored as cached singleton with LINQ to SQL generated and loaded table.
I am not sure how good LINQ to SQL generated entities work with queries to retrieve records for specified partial keys.
I world be interesting to see some benchmarks for LINQ to SQL loaded into memory collections similar as it was done for DataTable .
I am appreciate any suggestions regarding improving performance, as well as the regarding whole fallback approach.