Mvc Entity Framework - Setting up a query

See more:

C#

MVC

entity-framework

I am struggling with setting up a query in MVC, What I want to do is - for a tenant to login to his account he goes to his statement, and will see a yearly statement of Rent paid. I originally had it so it would be individual for each tenancy however since yesterday there was a change in plans.

Now the tenant can view all of his rent payments made whether there is a new tenancy or not as long as the property doesn't change. So I have the following tables I will only add the important parts of the table.

Property
Id (PK)
 
Payments
Id(PK)
PropId ( Related with Property Table - ID )
TenancyId ( nulled for now )
 
Tenancy
Id ( PK )
PropertyId ( Relationship with property )
 
Tenant
Id ( PK )
UserId
 
TenantTenancies ( Many to Many )
TenantId ( related off course with tenant table )
TenancyId( related off course with tenancy table )

I have the propertyId in the URL from propertyList so we already have access to the needed property. So I tried to do something like the following:

public ActionResult Index(int id, IndexViewModel model)
{
    model.PageTitle = "Tenant Statement";
    model.PageHeader = "Tenant Statement";

     // Get a list of tenancies where we see if a tenant in the ( manytomany TenantTenancy ) table
     // Where the User logged in's UserId matches the UserId in the tenants tenancy.
     var tenancies = db.Tenancies.Where(t => t.Tenants.Any(te => te.UserId == currentUserId));

     // The above query actually works as you can see at the bottom of the post.   
     

     //Then when I move on to actually getting the list of payments things get messed up...

    // List of pasyments, where propId has a value and tenancies has a value of Propertyid matching the Url property Id
    var items = db.Payments.Where(a => a.PropertyId.HasValue && tenancies.Any(b => b.PropertyId == id)).AsQueryable();

   return View(model);
}

The thing is the second query is just listing everything who was at that property, I need to find a way to seperate them, that being the UserId who is logged in. But I dont know what to do next?


[img]http://i.gyazo.com/e25dcb4c9df4760b5de993867c6888e9.png[/img]
Its just getting 1 value as I am only in 1 out of the two tenancies on that address, one being current. and one being the last person who lived there in 2010


EDIT: After looking more into it I found the actual full Query its doing:
[code]

{SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[PropertyId] AS [PropertyId], 
    [Extent1].[TenancyId] AS [TenancyId], 
    [Extent1].[Date] AS [Date], 
    [Extent1].[DateConfirmed] AS [DateConfirmed], 
    [Extent1].[Amount] AS [Amount], 
    [Extent1].[IsAgent] AS [IsAgent], 
    [Extent1].[IsLandlord] AS [IsLandlord], 
    [Extent1].[IsTenant] AS [IsTenant], 
    [Extent1].[PaymentType] AS [PaymentType]
    FROM [dbo].[Payments] AS [Extent1]
    WHERE ([Extent1].[PropertyId] IS NOT NULL) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Tenancies] AS [Extent2]
        WHERE ( EXISTS (SELECT 
            1 AS [C1]
            FROM  [dbo].[TenantTenancies] AS [Extent3]
            INNER JOIN [dbo].[Tenant] AS [Extent4] ON [Extent4].[Id] = [Extent3].[TenantId]
            WHERE ([Extent2].[Id] = [Extent3].[TenancyId]) AND ([Extent4].[UserId] = @p__linq__0)
        )) AND ([Extent2].[PropertyId] = @p__linq__1)
    ))}

[/code]

So in english I decided to write what its doing to my understanding...

[code]

select id
propid
tenid
date
dateconf
amount
isagent
islandlord
istenant
paymenttype

From payments table
where Payments.PropertyId has a value &&

select *

from tenancies
Where 1 exists in tenantTenancies

Innerjoin tenant
tenant4.Id = tenantId from tenantTenancies
Where
Tenancies.Id = TenancyId from TenantTenancies && Tenant.UserId == @p__linq__0) ??
and Tenancy.propertyId == URL
[/code]