LINQ is awesome

[peewee_RotA]

Thought I'd share this tutorial:
http://www.thereforesystems.com/linq-to-sql-join-on-multiple-conditions/


Very clever solution. I love it.

[peewee_RotA]

So the sum total of things that I learned about LINQ to Objects, LINQ data context mappings, and their relationships to ADO.NET is due in no small part to fun filled day of frustration. So please excuse the keywording that is about to follow because I want the next person to run into this problem to be able to google it quickly.

This is regarding
LINQ in C# using a DataContext to connect to a ADO.NET OleDB Jet4.0 provider that points to an Access 2000 .mdb file that includes a DATE/TIME field which is NULLABLE

DISCLAIMER: All of the below code snippits are Psuedo code. The actual method names probably differ slightly. It will not run if you copy and paste it.

So let's dive in:

#1 ... You CAN use a LINQ datacontext to connect to an OleDB Jet provider to an access file. This is done by creating your own mappings. The GUI for creating DBML files will tell you that this is unsupported. Technically this is true, which I will elaborate on later.

Code: [Select]

//Data context example using an OleDB connection string
public class myClass
{
     public myClass()
     {
          string connectionString = "provider=jetblahblahblah source=c:\myfile.mdb";
          MappingSource mySource = new AttributeMappingSource();
          //Data context can use this string but MUST include it's own mapping
          DataContext myContext = new DataContext(connectionString, MappingSource);
          Table<sampleTable> sampleTables = myContext.GetTable<sampleTable>();
     }
}

[Table (Name="sampleTable")]
public class sampleTable
{
     [Column()] //this doesn't need a name if the var name matches the columnname
     public int? CustomerNumber {get;set;}

     [Column(Name = "using")] //example of specifying the name for mapping and using a diff variable name
     public string? usingColumn{get;set;}

     [Column()] //example of specifying the name for mapping and using a diff variable name
     public Nullable<DateTime> myTimeStamp{get;set;}

     //Also notice the use of int?,string?, and Nullable Types. This is important for the nullable nature of access datatypes
}


[peewee_RotA]

#2 ... You CANNOT compare a LINQ to Objects source with a LINQ to SQL source in a from/select statement.

The easiest way to explain this is that LINQ is made up of 3 basic packages. LINQ to XML, LINQ to SQL, and LINQ to Objects. Based on the type of the source, the libraries responsible for evaluating the from/select are decided. If you are dealing with a type that has not been enumerated (like an IQueryable) then it will use LINQ to SQL. If you are dealing with a type that has been enumerated (like a list or dataset) then it will use LINQ to Objects. This will cause an exception either at compile or runtime. Usually the exception is not thrown until the resultant IQueryable is enumerated (in the debug watch, by using a foreach, etc...).

There is one and only one work around for this. You have to convert your enumerated data into a Array/List/Dataset/Table/var and extract it, individually, by column, and only then can you use a .contains().

Additionally, before setting up the below example, in order to use an enumerated type in a from/select, you have to use the .AsEnumerable() method that comes with the LINQ Extensions package.

Example:

Code: [Select]

void myfunc
{
     MyADOsourceContext adoDB = new MyADOsourceContext();
     MySQLsourceContext sqlDB = new MySQLsourceContext ();

     //IQueryable from an ADO "provider" source
     IQueryable<MyTable> ADOData = from p in adoDB.MyTables select p; //this was linq to sql

     //turning ADO data into a list. ToArray() is equally as effective. They are similarly non-generic collections
     var ArrayData = IQueryable.ToList();

     //using the enumerated object to select a single column (which will return another enumerated object)
     var ObjectData = from p in ArrayData.AsQueryable() where p.name.Equals("bob") select p.name; //this was linq to objects

     //comparing this column with a LINQ to SQL db
     var SqlStuff = from p in sqlDB where ObjectData.Contains(p.name) select p; //this is linq to sql again
}   

Additional considerations

 - You CAN use LINQ to SQL to create and use IQueryable<> objects in order to manipulate your data. However, there are many many reasons why you will end up enumerating data from Access. Not the least of which will be discussed in a following post.

 - You CANNOT use clever solutons like ArrayData.Any(p => p.name == "jim") because not only does LINQ to Objects use "In Memory" data that is incompatible with LINQ to SQL's IQueryable data, but most functions associated with Lists, Arrays, strings, and even DateTimes do not have a lambda expression equivalent.

 - LINQ to SQL statements are evaluated as SQL expressions behind the scenes, and are merely stored in a IQueryable until they are enumerated. LINQ to Object statements are evaluated immediately. So if you are doing a from/select on an existing enumerated object, it is an expensive call that fills the entire array immediately cycling through the conditions once for every entry in the table. So a statement using a table with 1000 rows will be run 1000 times even if your condition narrows the results down to 1.

[peewee_RotA]

#3 LINQ to SQL evaluates to an actual SQL expression that is run natively on your server (or in mycase, run against your access file). The proper syntax is determined by the provider selected when setting up your connection string (usually generated with the help of the server explorer interface in Visual Studio). Although you CAN map tables to a data context for OleDBs using JET providers, the proper syntax for access DOES NOT exist in the LINQ libraries. There are only a few instances where this applies, but they are very common exceptions.

The biggest, and most important, difference is that there are no CONVERT keywords in Access. Actually, there may be but the syntax differs from MSSQL and the rest of the supported flavors. You will find this if you ever try a .ToString() in your from/select.

For example the following will throw a run-time exception whenever the data is enumerated:

Code: [Select]

void myfunc
{
     MyADOsourceContext adoDB = new MyADOsourceContext();

     IQueryable<MyTable> ADOData = from p in adoDB.MyTables where singleCharacter.ToString() == "A" select p;
}

You can recreate the same Access Query code using the CONVERT keyword and you'll see that your exception is exactly the same as the error message that you get in access. This is an exception thrown by Access that are passed all the way back to the debugger.

You also cannot use any of the System.data.Linq.Extensions.SQLMethods. Most of these deal with dates, but know that they are off limits in Access.

Lastly, you cannot map a single character type from Access to a char or a char? in the Table Mapping. It has to be mapped as a string.

[peewee_RotA]

#4 Assume that ALL columns in an access database are nullable. This stems from so many things that it will only serve a purpose if I quick fire the information behind it.

ADO.NET connections use a special DBNull value. One of the major features of LINQ is to remove the DBNull quirk and allow any column mapping to be a nullable type.

One of the reasons why ADO.NET had to add the DBNull value has to do with the fact that access tables are generally always all nullable. Access also handles null in a different way than modern solutions. In fact MSSQL environments are often set up to not even allow null values!

When dealing with a LINQ to objects for an Access connection, since the query will be run for every single row regardless of the expected results, any null values WILL throw an exception at run time, so all values in all conditions must be checked for DBNull.

Mapping an Access DB to a DataContext helps to alleviate this using Nullable types, however you may still need to add a "MyColumn.HasValue" before any condition is checked. When using Datasets for a classic ADO.NET connection, this check will look like:

Code: [Select]

var myData = from p in MyDataSet.AsEnumerable() where !p.IsDBNull("ColumnName")
     && p.Field<string>("ColumnName") == "bob" select p;

Also, don't forget that if you are using nullable types, then you have to cast your conditions to match

Code: [Select]

[Column()]
public int? qty { get;set; }

...

IQuerable<sampleTable> myData = from p in db.sampleTables where (int)p.qty = 3 select p;


[peewee_RotA]

Now for the most important. The 8 hour headache that caused me to learn all of these things. The most evil, dispicable, undocumented piece of crap problem on earth.

#5 You CANNOT compare dates from an Access connection using LINQ to SQL. This is going to spread across a myriad of issues and reasons so I'm not even going to try to keep any structure to this post.

First and foremost the only datatype that can be mapped to a Date/Time type form Access is a Nullable<DateTime>. This HAS to be Nullable because without Nullable, you will crash during run time the first time you hit a null entry.

Date/Time and similar types in access are actually stored, by access, as types that resemble doubles. BUT you cannot map your column to a double type. You also CANNOT create a GETTER or a SETTER to do this for you because NO conversions to or from DateTime or double exist in a lambda expession format. Adding a custom expession<in,out> is also a complete and total dead end. (there is support for ToAODateTime and FromAODateTime in the extended DateTime libraries but, once again, there are no lambda equivalents that can be used in a from/select.

You cannot compare a DateTime using LINQ to SQL for an Access database because Date/Time is referenced in Access Queries with the syntax

Code: [Select]

# yyyy:mm:dd hh-mm-ss #where SQL queries use

Code: [Select]

'yyyy:mm:dd hh-mm-ss'
As we've already discussed, IQueryables are just wrappers for running actual SQL scripts against the DB so NO MATTER what you do, you get an "Injected SQL" issue.

You also cannot use string replace functions because they are stored in the SQL methods extension of LINQ and as we've already discussed, these methods are all "Injected SQL" that won't run in an Access Query.

So what is the solution?

DateTimes can only be compared if they are part of an enumerated list that is handled through LINQ to Objects. And what did we already discuss about LINQ to Objects? It's slow and it runs the condition against EVERY single row in the source. So this will cause NULL dates to always be compared during some step of the process.

The results of this then have to be turned into an array for each column which cam then only be compared to elements in a LINQ to SQL statement using .Contains().

There are only 2 things the ease the pain of this discovery.

First, you can filter the source of your array using a LINQ to SQL on other columns first. In my case I have a Status column in which I Only wanted entries of "P" for printed.

Secondly, anonymous types allow you to create records types on the fly to make comparison easy. So I'll put this all together for an example:

Code: [Select]

void somefunction
{
     myADOContext adoDB = new myADOContext();
     mySQLContext sqlDB = new mySQLContext();

     IQueryable<myTable> filteredList = from p in adoDB.myTables where p.Status.Equals("P") select p;

     var myEnumedList = filteredList.ToList();

     Nullable<DateTime> theOtherDay = DateTime.Now.AddDays(-2);
     var myFilteredEnum = from p in myEnumedList.AsEnumerable() where
                                        p.DateStamp > theOtherDay select p;

     var CleverArray = from p in myFilteredEnum.AsEnumerable()
                                  select new { idNum = p.id, customerNum = p.custNum };

     IQueryable<hisTable> sqlList = from p in sqlDB.hisTables where
                                                       CleverArray.Contains(new { idNum = p.id, customerNum = p.custNum }
                                                       select p;
}

You'll see all the crappy hoops and poor logic that we had to perform just to compare a DateTime compare. Well at least now you know how to do the same thing.

Oh and to pointout why this is not on the web. See the correct solution is to ditch the access file and migrate the data to SQL. I'm going to end up doing that at work as soon as possible, but this had to be done today and we have to contact a vendor to redo their driver in order to start saving things to a SQL db.

The other thing that you'll notice is that without mapping to a datacontext, using LINQ to Objects on an ADO.NET DataAdapter controlled table would have had the exact same logic anyway. The advantages I gain this way are mapped tables, Nullable types,  a better wrapper for my sql commands (where as command builders in ADO are hacky and error prone), and a smaller pool of data to enumerate from due to my first filter. I also can update any changes easier, but that was not in the scope of this project.

So anyway, this is now documented on the internet. Google should now find this solution.

[peewee_RotA]

I found a way to beat the "SQL injection":

Code: [Select]

MyADODataContext adoDB = new MyADODataContext();

var myQuery = from p in adoDB.myTables.AsEnumerable()
                  where p.DateField.HasValue && p.DateField > DateTime.Now.AddDays(-1)
                  select p;

foreach (myTable myItem in myQuery)
{
}

This will still place the database into memory before making the comparison, so it is still an incredibly expensive call, but it doesn't do so until it gets enumerated in the foreach loop. The main advantage here is that when you use the .contains() technique described earlier, you are limited to 2100 entires in your array. This allows you to create better logic to avoid that limitation. (The 2100 element limitation is a SQL limitation and not a .NET limitation)

However you REALLY do not want to have 2100 rows in memory anyway. So a combination of all of these techniques is required to get the desired results.

[[BTF] Reflex]

I hear ya brother....


































I just have no idea what you are talking about. 

[reaper]

interesting but wouldn't it be easier to switch the database away from Access since it's going to have problems when it gets large in size?

[peewee_RotA]

interesting but wouldn't it be easier to switch the database away from Access since it's going to have problems when it gets large in size?

That is the correct answer, and I thought that I explained that in the 2nd post...

But, it's not an option. The Access database controls a machine in a factory and that database is part of a proprietary software package that we have no control over. I'm going to whine and scream to the vendor to fix it, but even if I do, this had to be done now and that won't happen right away.