How do I group my LINQ query correctly?

Question

I am having an issue getting my LINQ query to output as I would like. I am unsure I am taking the right approach.

Tables:

I have two tables Contacts and Permissions with which I perform a LEFT OUTER JOIN.

Join Query:

from contact in Contacts
join permission in Permissions on contact.Id equals permission.ObjectId into permissionGrp
from p in permissionGrp.DefaultIfEmpty()
where (p==null && isAllowed) || (p!=null && /* ... further conditions */))
orderby /* ... ordering removed */
select new { contact, permission = p };

This matches the permissions to a contact where applicable, and null when no matching permission exists.

Desired

I don't wish to have duplicate contacts, I am only interested in the first Contact-Permission record. Like so:

Attempt:

So I assumed that I need to Group By my contact.Id and somehow select FirstOrDefault() on the permissions collection.

from contact in Contacts
join permission in Permissions on contact.Id equals permission.ObjectId into permissionGrp
from p in permissionGrp.DefaultIfEmpty()
where (p==null && isAllowed) || (p!=null && /* ... further conditions */))
orderby /* ... ordering removed */
group p by contact into contactPermissionsGrp
select new { contact = contactPermissionsGrp.Key, permission = contactPermissions.FirstOrDefault() };

Result:

Unfortunately this results in a NotSupportedException: Specific method is not supported.. But I am not sure if my approach is correct or a limitation of the LightSpeed ORM.

Any advise would be appreciated.

Answer 1

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]

Answer 2

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};

Answer 3

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.