Sage Developers' Blog

We provide a query editor in Sage 200 where you can write new queries right in the Sage 200 desktop, and for simple or moderate queries this works fine and is very convenient. For very complex queries however, the lack of  features like code-autocompletion means that there are better tools for the job. We may do some improvements to the query editor for the 2010 release, but it still likely to have some gaps compared to the likes of Visual Studio. Long term we want to provide a graphical query designer that can be used in our new report designer that is coming with v2010, but we are not there yet.

So in the meantime, what is the best way to create new Linq queries?

Well, one way you can do it is in Visual Studio – just make a new console or windows application, add a reference to our accounts or CRM model assembly and write the query. We have a Visual Studio project in the Sage 200 R&D team that contains many of the more complex queries that sit behind our out-of-the-box workspaces. We use this mainly for unit testing though, rather than for active query development.

The most popular tool in our team for query development is undoubtedly LinqPad. This is one of the best development aids I have ever used and it’s free too! Actually while there is a free option, we have paid the fee to activate the code auto-completion (a.k.a. Intellisense) feature. As someone or other put it: “It’s like turning the light on in a pitch black room”.

LinqPad is a tool for quickly developing and executing code snippets, queries or even small programs in a very quick and easy way. It let’s you do it in VB.Net, C#, regular SQL or entity SQL (eSQL). You can point it at any SQL Server database and it will generate a Linq to SQL model for you amazingly quickly, or you can point it at an existing Linq to SQL model or an entity framework model. You can add references to your own assemblies and code against them too. It really is an extremely flexible tool.

Anyway, enough plugging. Here is how you can use LinqPad to develop queries for Sage 200 workspaces based on the Sage 200 accounts model…

First you need to point LinqPad at the Sage 200accounts data model. To do this, click Add Connection in the top left of the screen:

Then, in the Data Context drop down, select Custom Linq to SQL DataContext.

In the Path to Custom Assembly browse to the folder where the Sage 200 desktop is installed (usually C:\Program Files\Sage\Sage200) and then to the DomainModels sub-folder. In that folder there are two assemblies. Select Sage.Accounting.DataModel.dll (the other one is the CRM Linq to SQL model).

Next, in the Full Name of Typed DataContext edit box, click Choose and select Sage.Accounting.IM.AccountsModel. This is the type that is the main entry point into the model.

To complete the form, you have to fill in the SQL database connection properties for the database you want to query. Once you have deployed the query in the Sage 200 desktop, the system will automatically select the proper connection string based on the current company that the user has selected. When you’re using LinqPad, you have to provide that yourself. The completed form should look something like this:

When you OK this LinqPad will list all the entities in the model on the left hand side of the screen. You can expand this to see all the properties of each entity. Here is a screenshot showing the some of the properties of SOPOrderReturnLine. Notice that it includes association properties that can get the SOPOrderReturn that the line belongs to, and the collection of SOPAllocationLines belonging to the line. This ability totraverse complex object graphs is a key feature of Linq.

With the model in place the next step is to choose what kind of query you want and to tell LinqPad that you want to use the Sage 200 account model you just added. To pick the model click “Use AccountsModel” in the top right corner of the screen. I usually select “C# statements” for my quey language, but you can also use VB.Net or SQL. Experiment. See what you prefer. Be aware though that only C# Statement will work in the Sage 200 Query Editor.

You are now ready to start developing queries against the Sage 200 accounts model. But before you do, you might want to add references to your assemblies or other assemblies. Remember that Linq queries are just .Net code and LinqPad allows you to import any .Net assembly. If you want to do that, hit F4 and browse to the assembly you want. You can also add namespaces (like a using in C#) or you can just refer to your types using their namespace qualified name (e.g. Sage.Accouting.IM.AccountsModel is the namespace qualified name for AccountsModel).

If you activate the auto-completion feature in LinqPad, you get the familiar intellisense type behaviour as you type:

Here is an example of a query that gets the favourite product for each customer. This post is not about the intricacies of Linq so I won’t explain the structure of the query here. However, the ability of LinqPad to quickly execute the query as it was being built up made it very easy to build the relatively complex nested Select to pull out the favourite item. Notice at the bottom of the query the use of the Dump() extension method. This causes LinqPad to output the results of the query to the output window. Once the query has executed, as well as seeing the results, you can look at final lambda expression that was sent to the Linq to SQL provider, the resulting SQL and the IL code (if you like that kind of thing) for the query. The SQL is pretty useful for performance tuning queries. LinqPad also helpfully puts the query execution time at the bottom of the screen – again useful for performance tuning.

Once you are happy with the query you can cut and paste it into the Sage 200 query editor. But there are a couple of small differences between the query editor and LinqPad that mean you have to make a some minor changes before the query editor will run the query.

First, LinqPad infers the data context, so you can type things like var q = SLCustomerAccounts. The query editor does not do that so you have to explicitly prefix the SLCustomerAccounts property with the context lie this var q = cxt.SLCustomerAccounts.

Second, to get the output form the query in LinqPad you use Dump(). In the query editor, you return the IQueryable that you want to execute instead.

After putting the customer favourites query into the query editor and making these adjustments, you can preview the results:

LinqPad has a load more features that will  increase your productivity and reduce your stress when developing Linq queries for Sage 200 workspaces. Hopefully this post has given you a good flavour of the tool and why we like it some much in our team!