Mvc实体框架 - 设置查询

本文介绍了Mvc实体框架 - 设置查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我正在努力在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.PropertyId (PK)PaymentsId(PK)PropId ( Related with Property Table - ID )TenancyId ( nulled for now )TenancyId ( PK )PropertyId ( Relationship with property )TenantId ( PK )UserIdTenantTenancies ( 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 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 idpropidteniddatedateconfamountisagentislandlordistenantpaymenttypeFrom payments tablewhere Payments.PropertyId has a value &&select *from tenanciesWhere 1 exists in tenantTenanciesInnerjoin tenanttenant4.Id = tenantId from tenantTenanciesWhereTenancies.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但是作为一个循环它会是这样的： b $ bThen 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实体框架 - 设置查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！