Michael Friis' Blog

I’ve just recorded a web cast demontrating joins with LinqtoCRM. The piece de resistance is a join across a many-to-many relationship with the intermediary class generated by CRMMetal:

var res = from u in p.Linq()
	join sr in p.Linq() on u.systemuserid.Value equals sr.systemuserid.Value
	join r in p.Linq() on sr.roleid.Value equals r.roleid.Value
        select new { u.fullname, r.name };

The equivalent example query in the CRM SDK is around forty lines, compared to four for LinqtoCRM. Watch the web cast here.

The standard Amazon Windows AMIs don’t come with the IIS 6 SMTP component installed. It can be added through the “Add or Remove Programs”->”Add/Remove Windows Components” util on Windows Server 2003 (full guide), but you need the installation media. Amazon has an article describing how to do just that here. You basically create an EBS volume from a snapshot they provide (2GB minimum size) and then attach it to your instance. It will show up as a drive in Windows, holding the contents of the two installation disks. Just point the installer at those and you should be good. Afterwards you can detach and delete the EBS volume.

You may remember my last post on this topic ended with a question. Just to recap, we have a table with a lot of rows that have geographical coordinates and we want to find a random subset that lies in a given map window. The problem with the query demonstrated last was that there are rows with the exact same coordinates and for those coordinates the query would always return the same row (namely the one with the highest intid).

I posted a question on Stackoverflow and Tom H. came up with a solution that was 90% there. The full query looks like this:

with cte as
(
    select
        intid,
        row_number() over
            (
                    partition by geoLat, geoLng
                    order by newid()
            ) as row_num,
        count(intid) over (partition by geoLat, geoLng) as TotalCount
    from
        documents d
	where
		d.geoLat < @maxLat
			and d.geoLat > @minLat
			and
			(
				((@maxLng > @minLng) and
					(d.geoLng < @maxLng and d.geoLng > @minLng))
				or
				((@maxLng < @minLng) and
					((d.geoLng > @minLng and d.geoLng < 180)
						or (d.geoLng > -180 and d.geoLng < @maxLng))
					)
			)
)
select top (@maxcount)
    dd.*, cte.intid, rand(cte.intid)
from
    cte,documents dd
where
    row_num = 1 + floor(rand() * TotalCount) and cte.intid = dd.intid
order by newid()

The query uses Common Table Expressions, which I’ve dabbled in before. Looking at the execution plan makes my head hurt, but it’s at least as fast as the original version. See the new version in action at the TEDBot site.

There’s a new version of LinqtoCRM out, get it here. Petteri Räty has rewritten the query-generation engine so that arbitrary selectors are supported. He has also squashed some nasty predicate-bugs and added more unit tests. I’ve implemented a CRMMetal tool that generates classes for many-to-many relationships and joins across these are now supported.

I’ve also recorded a new screencast for your amusement.