score:1

Accepted answer

Answer question with more questions

  • Is your query possible in raw SQL in the database you are targeting? If the answer is No then what hope have you of replicating it in LINQ?

I personally don't think that what you are trying to achieve is possible to do via LINQ against MySQL. It would be possible in MS SQL in raw T-SQL by defining an expression and attaching a RANK() column to it then doing a query on that expression.

I feel that your available solutions are:

  1. Find out how to write this query using raw SQL in your native dialect. Lightspeed will let you execute raw SQL and it will even (where you return enough columns) rehydrate that custom query into Entitys (however I don't think that is what you are after in this case).

  2. Give up on reducing the "duplicates" efficiently in the database. Pull the duplicates into memory and then reduce them in memory with LINQ queryies against the IEnumerable set that you get back.

  3. Change your database architecture so you can have a simpler query. Sometimes in situations like this I will find honour in having a column on the Contact table such as "MostSignificantPermssion". That has a number of advantages:

    • Simpler query to get the Contact and the ONE significant Permission record.
    • Makes it more obvious to other developers that one of the Permissions has a special significance.

Options!

Appendix - Example of implementing (1) in MS SQL

WITH LastUsagePerPerson AS (
    SELECT 
        ULE.PersonId, 
        ULE.[Device], 
        ULE.[AppVersion], 
        ULE.[CreatedOn], 
        ROW_NUMBER() OVER(PARTITION BY ULE.PersonId ORDER BY ULE.CreatedOn DESC) AS rk
    FROM [dbo].[UsageLogEntry] ULE
    )


SELECT 
     [FirstName]
    ,[LastName]
    ,[EmailAddress]
    ,[EmailAddressUnverified]     
    ,[MobileNumber]
    ,[MobileNumberUnverified]
    ,[LastDeviceUsed] = LastUsagePerPerson.Device
    ,[LastAppVersion] = LastUsagePerPerson.AppVersion
    ,[LastDeviceUsage] = LastUsagePerPerson.CreatedOn
    ,[LastLoggedInOn]

  FROM [dbo].[Person] P
    LEFT JOIN LastUsagePerPerson ON P.Id = LastUsagePerPerson.PersonId

WHERE rk = 1

ORDER BY [Id]

score:-2

I have understood what you are trying to get and I have solved your problem,just follow the below code what I did...

select * from contacts as a 

 left join permissions as b 

   on a.ContactId = b.ContactId

     group by a.ContactId ;

I have got the requeried result using the above code which you were trying to get.Just try that,your problem will be solved.

score:0

I don't know what Lightspeed can or can't. Try to simplify you LINQ Query In EF i would do something like this.

from c in Contacts
let p = (from p in permission where p.ObjectId == c.Id select p).FirstOrDefault()
select new { ContactID = c.Id,
             Name = c.Name,
             Permission = p.PermissionId,
             Permitted = p.Permitted};

Related Query