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屋!