Mvc实体框架 - 设置查询 [英] Mvc Entity Framework - Setting up a query

问题描述

我正在努力在MVC中设置查询，我想要做的是 - 对于租户登录他的帐户，他会转到他的陈述，并会看到每年的租金声明。我最初拥有它所以每个租赁都是个人的，但是从昨天起，计划发生了变化。



现在，只要房产没有变化，房客可以查看他是否有新房租的所有租金。所以我有以下表格，我只会添加表格的重要部分。

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 )

我在propertyList的URL中有propertyId，所以我们已经可以访问所需的属性。所以我尝试做类似以下的事情：

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

问题是第二个查询只列出了该属性的所有内容，我需要找到一种方法来分隔它们，即UserId谁登录了。但我不知道下一步该做什么？





[img] http://i.gyazo.com /e25dcb4c9df4760b5de993867c6888e9.png [/ img]

它只获得1个值，因为我在该地址的两个租约中只有1个，其中一个是最新的。一个是2010年生活在那里的最后一个人





编辑：仔细研究后我发现它实际上是完整的查询：

[code]

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


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]



所以在英语中我决定写下它对我的理解。 ..



[code]



select id

propid

tenid

date

dateconf

金额

isagent

islandlord

istenant

paymenttype



来自付款表

其中Payments.PropertyId有一个值&&



选择*



来自租约

其中1存在于tenantTenancies < br $>


Innerjoin房客

tenant4.Id =租户来自租户的租约

其中

租约.Id =租户来自TenantTenancies&& Tenant.UserId == @ p__linq__0）??

和Tenancy.propertyId == URL

[/ code]

[/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]

推荐答案

对，更新您的租赁查询以过滤属性：

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

然后你应该可以循环你的租约并且只需支付款项。

我不能记住它的linq但是作为一个循环它会是这样的：< br $> b $ b

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>

这篇关于Mvc实体框架 - 设置查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！