Click here to Skip to main content
15,850,685 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:


C#
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]
SQL
{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]
Posted
Updated 9-Jul-14 4:24am
v5

1 solution

Right, update you tenancies query to filter on property as well:

var tenancies = db.Tenancies.Where(t => t.Tenants.Any(te => te.UserId == currentUserId) && t.PropertyId == id);


Then you should be able to do loop around your tenancies and just pull off the payments.
I cant remember the linq for it but as a loop goes it would be something like this:

var myPayments = new List<payment>
foreach(var tenancy in tenancies){
  myPayments.AddRange(tenancy.Payments);
}</payment>
 
Share this answer
 
Comments
michaelsalter92 9-Jul-14 9:33am    
Its annoying as the first query ( tenancies ) is perfect only pulls one back, but the second query where I make it to a list isnt working. Its just adding them all instead of just the logged in users payments.
Pheonyx 9-Jul-14 10:02am    
I don't know why you do this:

a => a.PropertyId.HasValue << yet you never compare it against the property id.
you could do:
var items = db.Payments.Where(a => a.PropertyId.HasValue && a.PropertyId == id && tenancies.Any(b => b.PropertyId == id)).AsQueryable();
michaelsalter92 9-Jul-14 10:12am    
Still same issue, What the problem is, there is two tenancies under the same property. One from 2010 an old tenant, then me a current tenant 2014.

What its doing is in var tenancies its limiting it down going okay this property has x amount of tenancies under it, and instead of going okay I want THIS tenancy only as its the only one the logged in user is in.

Its just ouputting all tenancies tenants instead of just 1!

So seems to be listing all tenancies at that property instead of just the one what my user has been in. Full payments table:

http://gyazo.com/32df7b9bb4d0964be575b0096a93a42a

Remember you can get Tenancy Id in property.
Pheonyx 9-Jul-14 11:51am    
Well, you don't link a payment to a tenant directly so how do you expect to be able to pull the two together?
If you have a tenancy that has 4 people on, when you look at a payment how are you going to know which of those people the payment is for?,
That aside, if you take your tenancy lists for the logged in user and filter that by the property.. then you have the "tenancies" for that property for that user, then you can loop through the tenancies and pull out the payments as they are linked. That would be the best way to get as close as you can.
That way you can see all the payments for the property for all the tenancies that user has been part of. However, as you have no link on the payment for a tenant, if there are multiple tenants on a tenancy you won't be able to filter them out.
michaelsalter92 9-Jul-14 11:56am    
I was pulling them through tenancy. Tbh the old method they wanted worked great. Each tenancy has there own payments that's it. But since they want all tenancies merged if he same property its caused me issues.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900