Archive

Posts Tagged ‘magiq-to-sql’

Magiq to Entities – An update

May 7, 2011 2 comments

The past days I’ve been working on making Magiq work with Entity Framework.

First I tried to work with the MetadataWorkspace and I gave up, ’cause its really impossible to get the mapping information. Luckily, I found Linq to Edmx project who does exactly what I needed.

Then I had some troubles working with the Queryables generated by the ObjectSets. Basically, the expression generated calls methods that doesn’t work and are there only to give information in the expression. So the expression is not something you can compile and execute. As big part of Magiq is to manipulate expression trees, that was a really big problem. I needed to figure out how to change the source queryable (I mean, the queryable where all the methods are applied to) to a simple ObjectSet. Besides it works now, I’m still working to get to a nicer way (you don’t want to know what I have done to make it work…).

Since I changed the insert API from having the new item expression (the new{…}) outside to have it as a select, I had to break the query in two, the select and the “rest”. Ok, that gave me more headaches :).

But the biggest problem was when Entity Framework started to change the order of the columns in the select (and add new columns). Let me explain how Linq to Sql works:

If you do this:

items.Where( ... ).Select( x => new { A = x.Prop1, B = x.Prop2, C = x.Prop2 /*again*/ });

Linq to sql transate it to:

SELECT prop1, prop2, prop2 FROM items WHERE ...

But Entity Framework removes the repeated columns and maybe reorders them, giving you something like:

SELECT prop2, prop1 FROM items WHERE ...

You don’t have any idea of the reflection tricks I had to make to get those property-select columns mapping.

But well, those are not bad news at all. I mean I could work around them and I can say Magiq now supports Entity Framework (with a few restrictions). Those are:

  • Only Table per class hierarchy inheritance is supported. I’m currently working on the Table per concrete class and Table per type strategies
  • Only mapped associations are supported. This means associations based on foreign keys are not (yet).
  • Only mapped POCOs are supported. This means Self Tracking entities and Code First are not (yet).

Keep in touch for the updates 🙂

Advertisements

Magiq: New release and API Changes

April 28, 2011 Leave a comment

New release of Magiq, supporting Linq-to-sql. This should be the last one for Linq-to-sql, the next one I hope will have the minimal support for Entity Framework. You can download it from here.

For those reading this for the first time (?), Magiq is a framework that let you do mass operation in a Linq fashion.

So… as Magiq will be supporting Entity Framework soon, it made sense to change the API to make it support Entity Framework limitations. Also, I found a better Insert API (the one that always made me doubt!). The changes are:

Query extension method changes
The new method looks like this:

country.Query(x => x.Cities); 

It will return an IQueryable that will let you do all the Magiq stuff.

No more IEnumerable support
This is a hard one. You can only do Magiq stuff between IQueryables. If you have an IEnumerable collection, use the Query extension method of it parent entity.

New Insert API

destination.Insert( source.Where( condition ).Select( new Item{ ... } ));

Cool, isn’t it? 🙂

Entities extension methods
Because we no longer support extension methods over collections but over the entity, there are some extension methods for improving the code readavility.

Delete

entity.Delete(x => x.Collection.Where( condition ));
//Instead of 
entity.Query(x => x.Collection).Where( condition ).Delete();

Update

entity.Update(x => x.Collection.Where( condition ).Set(x => x.Prop, value));
//Instead of 
entity.Query(x => x.Collection).Where( condition ).Set(x => x.Prop, value).Update();

Query

entity.Query(x => x.Collection.Where( condition ));
//Instead of 
entity.Query(x => x.Collection).Where( condition );

Insert

entity.InsertInto( x => x.Collection, source.Where( condition ).Select( new Item{ ... } ));
//Instead of
entity.Query(x => x.Collection)
      .Insert( source.Where( condition ).Select( new Item{ ... } ));

Magiq-to-Sql: Compiled queries support

November 25, 2010 Leave a comment

Back on the road with Magiq, at least for a while. I’m working on the development of a framework that could make full use of the Named Scopes feature of Magiq. The problem is that linq-to-sql needs compiled queries in order to speed up the performance and the current API is awful. So I tried to give Magiq the ability of handling compiled queries.

Since Linq is all about expression trees, I knew it will be possible to get from

var value = 3000000;
var query = country.Cities.Query( x => x.Population > value)
                          .OrderBy( x => x.Population);

to something like

var value = 3000000;
var expresson = (dc, id, value) => dc.GetTable<City>()
                                     .Where( x=>x.Id == id )
                                     .SelectMany( x=> x.Cities)
                                     .Where( x => x.Population > 100000 )
                                     .OrderBy( x => x.Population);

var query = CompiledQuery.Compile( expression );

Well, two hours of playing with the Expressions and all worked. The only/main problem is that Expressions instances are created every time so each expression object has a different HashCode and it’s impossible to cache the compiled queries by expression. Sadly, I had to add a parameter named “key” for identifying the compiled query.

This si how the final API looks like:

var value = 3000000;
var query = country.Cities.CompiledQuery( "large-cities-by-country", 
                                          (q,p) => q.Where( x => x.Population > p)
                                                    .OrderBy( x => x.Population),
                                          value );
  • The query will be identified as “large-cities-by-country”.
  • We need to pass the expression to be applied to the collection and the parameters, because we don’t want to hardcode the values in the query.

A small benchmark I’ve done with a simple model executing 5000 queries on a new DataContext each gives, this result:

  • magiq – not compiled: 36457 millisecond
  • magiq – compiled: 22295 millisecond
  • linq2sql – compiled: 22194 millisecond

Magiq: BulkCopy support

February 19, 2010 Leave a comment

With all the behavior stuff, I have normalized the design in favor of new Magiq providers. Now there’s a MagiqToDatabases abstract class that only needs a few methods to implement. Also, there is a normalization in the operations: Now Magiq have the classes DatabaseInsert, DatabaseUpdate, DatabaseQuery and DatabaseDelete, which contains properties like the columns to update, the expressions of the values, etc.

For implementing BuilkCopy, there was only two things needed to override:

  • The way Magiq-to-objects inserts into a SqlServer based destination: For example, for a Linq-to-Sql table, the way Magiq-to-objects inserts is using the Insert method of Table. The idea is to override that with a SqlBulkCopy. Note: this “way Magiq-to-objects inserts” is called IInsertDestination.
  • Use magiq-to-objects instead any SqlServer insert strategy defined: For using BulkCopy when the source belongs to the same provider, avoiding the regular insert…select… statement

So, following the behavior schema, Magiq provides two interfaces for all this: IObjectsInsertBehavior and IInsertBehavior.

  • IObjectsInsertBehavior provides the way for overriding the IInsertDestination.
  • IInsertBehavior provides the way for overriding the strategy to use, so we can avoid using the default strategy

Also, it needed a way to know if the destination is a SqlServer. Well… all the database operations and strategies implements an interface called IDatabaseBasedExecution that provides a IDatabaseAdapter. And this last interface give us a IDialect that could be a SqlServerDialect.

Ok, ok, but show me some code!!

public class BulkCopy : MagiqBehavior, IObjectsInsertBehavior, IInsertBehavior
{
	public int BatchSize { get; set; }
	public int Timeout { get; set; }

	public BulkCopy()
	{
		BatchSize = 0;
		Timeout = 30;
	}

	private static bool Applies(IDatabaseBasedExecution execution)
	{
		return execution != null && execution.DatabaseAdapter.Dialect is SqlServerDialect;
	}
	
	public IInsertDestination<TNew> GetInsertDestination<TSource, TDestination, TNew>(IInsert<TSource, TDestination, TNew> insert)
		where TDestination : class
		where TNew : class, TDestination, new()
	{
		var insertDestination = Next<IObjectsInsertBehavior>().Call(x => x.GetInsertDestination(insert));
		var databaseOperation = insertDestination as IDatabaseBasedExecution;

		if (Applies(databaseOperation))
			return new BulkCopyDestination<TSource, TDestination, TNew>(insert, databaseOperation.DatabaseAdapter)
					   {
						   BatchSize = BatchSize,
						   Timeout = Timeout
					   };

		return insertDestination;
	}

	public IInsertStrategy GetInsertStrategy<TSource, TDestination, TNew>(IInsert<TSource, TDestination, TNew> insert) where TDestination : class where TNew : class, TDestination, new()
	{
		var strategy = Next<IInsertBehavior>().Call(x => x.GetInsertStrategy(insert));
		var databaseOperation = strategy as IDatabaseBasedExecution;

		if (Applies(databaseOperation))
			return new InsertStrategy<TSource, TDestination, TNew>(insert);

		//overrides the strategy with magiq-to-objects
		return strategy;
	}

	public int Execute(IInsertStrategy strategy)
	{
		//default behavior
		return Next<IInsertBehavior>().Call(x => x.Execute(strategy));
	}
}

And the way to use it:


var sellsFromSomeRepository = ...;

records.From(sells)
       .Using<BulkCopy>()
       .Insert(x => new Record
                     {
                        Type = RecordType.Sell,
                        Description = "Sell from " + x.Date,
                        Amount = x.Total
                     });

And of course, if you use BulkCopy for inserting into a non SqlServer repository, the plugin continues without changes, so it could be used in a testing environment in which the repositories are setted to a List.

Magiq 0.3.0 available!

February 17, 2010 Leave a comment

I’m glad to announce that the new version of Magiq is available.
Magiq now support:

  • Plugin model
  • BulkCopy as a Plugin for using SqlBulkCopy when inserting into sql server from any source.

And also there was a huge refactoring in order to have simplicity when implementing new providers.

You can download this version from here.

Magiq-to-sql: Associations issue

December 25, 2009 Leave a comment

Following the Magiq saga, I started working on composite primary keys support for magiq-to-sql. With this feature we will be releasing the first version of Magiq, so we are very exited about it.

The first thing I have to say is there isn’t much information out there, so I use the “try&error” technique. My first approach was to declare the primary keys as several columns with “IsPrimaryKey=true” and, for the associations, using all the columns separated with commas in the ThisKey and OtherKey properties. Of course, I didn’t made up that, I saw it here. Good enough, it worked fine!

But (and there’s always a “but”)… doing the unit tests for this feature I realized we didn’t tested the insert and update of an association. I mean, this:

items.Set( x => x.AReference, x => x.AnotherReferenceOfTheSameType ).Update();

and

items.Insert( x => new Thing{ Reference = x.Reference } ).Into( things );

The problem here is that the generated query for this kind of expressions uses joins and subselects and, since I’m using a different query for each update set or value to insert, linq-to-sql will generate a different where for each (and a the parameters will use different table aliases).

The solution I see is to generate one single select for each property but, as I said here, it needs to use a dynamically generated class. I’m afraid I have no choice. I’ll post about it in the next days. If it works, we’ll be releasing Magiq 🙂

Oh, right, merry christmas.