将SQL视图转换为Linq [英] Converting an SQL view to Linq

问题描述

大家好，

我不确定这是否是正确的论坛，如果不是，请移动它

I'm not sure if this is the right forum, if not please move it

我的办公室正在重写我们的应用程序并从VB切换到C＃和实体框架。 我在使用左外连接加入多个表方面取得了一些成功，但我对如何转换如下的sql视图有限制：

My office is in the process of re-writing our app and switching from VB to C# and the entity framework.  I've had some success in joining multiple tables all with left outer joins but I'm at my limits on how to convert an sql view like the following:

SELECT         dbo.Addresses.AddressId，dbo.AddressTypes.AddressType，dbo.Addresses.AddressTypeId，dbo.Addresses.ParentAddressId，dbo.Addresses.AddressCode，dbo.Addresses.AddressNumber，dbo.Addresses.Address，


                          dbo.Addresses.SubAddress，dbo.Addresses.Direction，dbo.Addresses.City，dbo.Addresses.StateId，dbo.Addresses.CountryId，
ISNULL（dbo.Addresses.AddressNumber + ''， ''）+ ISNULL （dbo.Addresses.Direction +''，''）


              ;            + ISNULL（dbo.Addresses.Address +''，''）+ ISNULL（dbo.Addresses.Suffix +''，''）+ ISNULL（dbo.Addresses.SubAddress
+''，''）AS FullAddress，dbo.Addresses.RegionId，dbo.Addresses.CountyId，

                          dbo.Addresses.OccupancyTypeId，dbo.Addresses.PropertyUseTypeId，dbo.Addresses.Comment，dbo.States.StateAbbr，dbo.States.State，
dbo.Regions.Region，dbo.Regions.RegionCode，dbo.Counties。县，

                          dbo.Counties.CountyCode，dbo.Countries.Country，dbo.OccupancyTypes.OccupancyType，dbo.OccupancyTypes.OccupancyTypeCode，
dbo.PropertyUseTypes.PropertyUseType，dbo.Party.PartyName，

                          dbo.PropertyUseTypes.PropertyUseTypeCode，dbo.UserDefFields.UserDefFieldId，dbo.UserDefFields.FieldDesc，dbo.UserDefValues.UserDefValueId，
dbo.UserDefValues.UserDefValue，dbo.Addresses.ZipId，dbo.Zips.Zip，

                          dbo.AddressParties.PartyID，dbo.Addresses.Latitude，dbo.Addresses.Longitude，dbo.Addresses.Inactive，dbo.Addresses.DefaultPass，
dbo.Addresses.Suffix，dbo.AddressTypes.AgencyId，dbo.Addresses。 LegalDesc，

                          dbo.AddressParties.Inactive AS PAInactive，dbo.AddressParties.RoleTypeId，dbo.Addresses.POBox，dbo.AddressParties.ExternalValue，
dbo.Addresses.DateUpdated，dbo.Addresses.DateInserted，dbo.Addresses.ReportId，

                          dbo.Addresses.Map，dbo.Addresses.Block，dbo.Addresses.Lot，dbo.Addresses.TaxParcel，dbo.Addresses.ExternalId，dbo.Addresses.Schedule

从   &NBSP ;        dbo.UserDefFields RIGHT OUTER JOIN

                          dbo.UserDefValues ON dbo.UserDefFields.UserDefFieldId = dbo.UserDefValues.UserDefFieldId RIGHT OUTER JOIN

                          dbo.Party RIGHT OUTER JOIN

                          dbo.Addresses LEFT OUTER JOIN

                          dbo.Zips ON dbo.Addresses.ZipId = dbo.Zips.ZipId LEFT OUTER JOIN

                          dbo.AddressTypes ON dbo.Addresses.AddressTypeId = dbo.AddressTypes.AddressTypeId LEFT OUTER JOIN

                          dbo.States ON dbo.Addresses.StateId = dbo.States.StateId LEFT OUTER JOIN

                          dbo.Regions ON dbo.Addresses.RegionId = dbo.Regions.RegionId LEFT OUTER JOIN

                          dbo.Counties ON dbo.Addresses.CountyId = dbo.Counties.CountyId LEFT OUTER JOIN

                          dbo.Countries ON dbo.Addresses.CountryId = dbo.Countries.CountryId LEFT OUTER JOIN

                          dbo.OccupancyTypes ON dbo.Addresses.OccupancyTypeId = dbo.OccupancyTypes.OccupancyTypeId LEFT OUTER JOIN

                          dbo.PropertyUseTypes ON dbo.Addresses.PropertyUseTypeId = dbo.PropertyUseTypes.PropertyUseTypeId LEFT OUTER JOIN

                          dbo.AddressParties ON dbo.Addresses.AddressId = dbo.AddressParties.AddressID ON dbo.Party.PartyID = dbo.AddressParties.PartyID
ON dbo.UserDefValues.RecordId = dbo.Addresses.AddressId

SELECT        dbo.Addresses.AddressId, dbo.AddressTypes.AddressType, dbo.Addresses.AddressTypeId, dbo.Addresses.ParentAddressId, dbo.Addresses.AddressCode, dbo.Addresses.AddressNumber, dbo.Addresses.Address,
                         dbo.Addresses.SubAddress, dbo.Addresses.Direction, dbo.Addresses.City, dbo.Addresses.StateId, dbo.Addresses.CountryId, ISNULL(dbo.Addresses.AddressNumber + ' ', '') + ISNULL(dbo.Addresses.Direction + ' ', '')
                         + ISNULL(dbo.Addresses.Address + ' ', '') + ISNULL(dbo.Addresses.Suffix + ' ', '') + ISNULL(dbo.Addresses.SubAddress + ' ', '') AS FullAddress, dbo.Addresses.RegionId, dbo.Addresses.CountyId,
                         dbo.Addresses.OccupancyTypeId, dbo.Addresses.PropertyUseTypeId, dbo.Addresses.Comment, dbo.States.StateAbbr, dbo.States.State, dbo.Regions.Region, dbo.Regions.RegionCode, dbo.Counties.County,
                         dbo.Counties.CountyCode, dbo.Countries.Country, dbo.OccupancyTypes.OccupancyType, dbo.OccupancyTypes.OccupancyTypeCode, dbo.PropertyUseTypes.PropertyUseType, dbo.Party.PartyName,
                         dbo.PropertyUseTypes.PropertyUseTypeCode, dbo.UserDefFields.UserDefFieldId, dbo.UserDefFields.FieldDesc, dbo.UserDefValues.UserDefValueId, dbo.UserDefValues.UserDefValue, dbo.Addresses.ZipId, dbo.Zips.Zip,
                         dbo.AddressParties.PartyID, dbo.Addresses.Latitude, dbo.Addresses.Longitude, dbo.Addresses.Inactive, dbo.Addresses.DefaultPass, dbo.Addresses.Suffix, dbo.AddressTypes.AgencyId, dbo.Addresses.LegalDesc,
                         dbo.AddressParties.Inactive AS PAInactive, dbo.AddressParties.RoleTypeId, dbo.Addresses.POBox, dbo.AddressParties.ExternalValue, dbo.Addresses.DateUpdated, dbo.Addresses.DateInserted, dbo.Addresses.ReportId,
                         dbo.Addresses.Map, dbo.Addresses.Block, dbo.Addresses.Lot, dbo.Addresses.TaxParcel, dbo.Addresses.ExternalId, dbo.Addresses.Schedule
FROM            dbo.UserDefFields RIGHT OUTER JOIN
                         dbo.UserDefValues ON dbo.UserDefFields.UserDefFieldId = dbo.UserDefValues.UserDefFieldId RIGHT OUTER JOIN
                         dbo.Party RIGHT OUTER JOIN
                         dbo.Addresses LEFT OUTER JOIN
                         dbo.Zips ON dbo.Addresses.ZipId = dbo.Zips.ZipId LEFT OUTER JOIN
                         dbo.AddressTypes ON dbo.Addresses.AddressTypeId = dbo.AddressTypes.AddressTypeId LEFT OUTER JOIN
                         dbo.States ON dbo.Addresses.StateId = dbo.States.StateId LEFT OUTER JOIN
                         dbo.Regions ON dbo.Addresses.RegionId = dbo.Regions.RegionId LEFT OUTER JOIN
                         dbo.Counties ON dbo.Addresses.CountyId = dbo.Counties.CountyId LEFT OUTER JOIN
                         dbo.Countries ON dbo.Addresses.CountryId = dbo.Countries.CountryId LEFT OUTER JOIN
                         dbo.OccupancyTypes ON dbo.Addresses.OccupancyTypeId = dbo.OccupancyTypes.OccupancyTypeId LEFT OUTER JOIN
                         dbo.PropertyUseTypes ON dbo.Addresses.PropertyUseTypeId = dbo.PropertyUseTypes.PropertyUseTypeId LEFT OUTER JOIN
                         dbo.AddressParties ON dbo.Addresses.AddressId = dbo.AddressParties.AddressID ON dbo.Party.PartyID = dbo.AddressParties.PartyID ON dbo.UserDefValues.RecordId = dbo.Addresses.AddressId

推荐答案

您的代码很难阅读，因为您在发布代码之前没有使用格式代码来格式化代码。工具栏上HTML图标旁边的图标将在发布之前格式化代码。您也可以编辑帖子并重新发布。

Your code is hard to read, becuase you didn't use the format faction to format the code before posting it. The icon next to the HTML icon on the toolbar will format the code before posting it. You can also edit your post and repost.

这篇关于将SQL视图转换为Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！