I just found a great little tool called Linqer. This tool generates LINQ queries from SQL statement. Sounds interesting? I said to a friend that if it was working, I would buy me a license.

I had this SQL query that I wanted to transform to LINQ-to-Entities:

SELECT H.*
FROM dbo.CaledonUHHoldings AS H
INNER JOIN dbo.CaledonSeries AS S
ON S.ID = H.SeriesID
INNER JOIN dbo.CaledonFund AS F
ON F.ID = S.FundID
INNER JOIN (
	SELECT F2.ID, MAX(H2.Date) AS Date
	FROM dbo.CaledonUHHoldings AS H2
	INNER JOIN dbo.CaledonSeries AS S2
	ON S2.ID = H2.SeriesID
	INNER JOIN dbo.CaledonFund AS F2
	ON F2.ID = S2.FundID
	GROUP BY F2.ID
) AS A
ON A.ID = F.ID
AND A.Date = H.Date
WHERE H.UnitHolderID = 101

Everybody (!) quickly understand that I am joining 3 tables to get the max date by fund (the inner query) before joining a second time the 3 same tables to get the details. I know there are probably other ways of doing it but my target when I wrote this query was to get the data I wanted.

I am not so bad in LINQ but not an expert. I wanted to get the same query as above but in LINQ so that I could query my model instead of building a function in SQL to query.

Using the trial version of the tool, I connected it to my database AND to my model (.edmx). I pasted my SQL query, ran the query directly in the tool (just to ensure I get proper results) and press the button to convert my query to LINQ. On the right part of the dialog, I then got this LINQ query:

From H In db.CaledonUHHoldings
Join A In ( 
	(From H2 In db.CaledonUHHoldings
	Group New With {H2.CaledonSeries.CaledonFund, H2} By ID = CType(H2.CaledonSeries.CaledonFund.ID,Int32?) Into g = Group 
	Select 
	  ID = CType(ID,Int32?),
	  Date = CType(g.Max(Function(p) p.H2.Date),DateTime?))
	)
      On New With { .ID = H.CaledonSeries.CaledonFund.ID, .Date = H.Date }
  Equals New With { .ID = CInt(A.ID), .Date = CDate(A.Date) }
Where CLng(H.UnitHolderID) = 101
Select 
  H.ID,
  UnitHolderID = H.UnitHolderID,
  SeriesID = H.SeriesID,
  Date = H.Date,
  Holdings = H.Holdings,
  MarketValue = H.MarketValue,
  TimeStamp_TS = H.TimeStamp_TS,
  MAJDate = H.MAJDate,
  MAJLogin = H.MAJLogin

I then copied that query to my project and did minor changes (like changing db for my context) and got it working quickly.

Linqer saved me a lot of time on this query. This query alone saved me more than 60$ USD which is the cost of the license. Now it’s time for me to place my order and for you to give it a try!