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!