How to organize LINQ query correctly?

Question

I am having trouble getting my LINQ request as I would like. I am not sure that I am using the correct approach.

Tables:

I have two tables Contactsand Permissionswith which I execute LEFT OUTER JOIN .

Join request:

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 corresponds to the permissions for the contact, where applicable, and nullif there is no corresponding permission.

Desired

I do not want to duplicate contacts, I'm only interested in the first Contact-Permission entry. For example:

Attempt:

So, I suggested that I need Group Bymy contact.Idand somehow select FirstOrDefault()in the permission 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:

, NotSupportedException: Specific method is not supported.. , LightSpeed ORM.

.

Answer 1

• SQL ? "", LINQ?

, , , LINQ MySQL. MS SQL T-SQL, RANK(), .

, :

• , , raw SQL . Lightspeed SQL, ( ) Entitys ( , , ).

• "" . , LINQ IEnumerable, .

• , . , "MostSignificantPermssion". :

  • , .
  • , .

- (1) 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

, Lightspeed . LINQ EF - .

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 realized what you are trying to get, and I solved your problem, just follow the code below, what I did ...

select * from contacts as a 

 left join permissions as b 

   on a.ContactId = b.ContactId

     group by a.ContactId ;

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