This question is locked. New answers and comments are not allowed.
Does anyone know of a TSQL to Linq converter that works fairly well? I have a ton of TSQL views that I would like to convert to Linq for use with OpenAccess, but since I'm basically a Linq noob at this point, it is taking me quite a long time.
Here is one I am currently working on and failing at. (If you get really bored and want to try and convert this I won't stop you I promise!)
Also I didn't write this TSQL so please don't judge me by it! ;)
Here is one I am currently working on and failing at. (If you get really bored and want to try and convert this I won't stop you I promise!)
SELECT DISTINCT |
dbo.ptApprovedCertification.PK AS ptApprovedCertificationPK, dbo.ptAConventionalCertification.PK AS ptAConventionalCertificationPK, |
dbo.osCompanyModule.PK AS osCompanyModulePK, dbo.pmUnit.PK AS pmUnitPK, dbo.pmUnitResident.PK AS pmUnitResidentPK, |
dbo.ptAMember.PK AS ptAMemberPK, dbo.osCompanyWorker.PK AS osCompanyWorkerPK, dbo.osEntity.PK AS osEntityPK, dbo.osParticipant.PK AS osParticipantPK, |
dbo.osEntity.EntityID, dbo.ptAMember.SocialSecurityNumber AS TaxID, dbo.ptAMember.FirstName, dbo.ptAMember.MiddleName, dbo.ptAMember.LastName, |
CAST(DATEDIFF(d, dbo.ptAMember.DateOfBirth, GETDATE()) / 365 AS INT) AS Age, dbo.ptAMember.DateOfBirth, dbo.ptAMember.Gender, |
dbo.fnptGetAMembersRaceList(dbo.ptAMember.PK) AS Race, dbo.ptAMember.EthnicityDesc AS Ethnicity, dbo.ptAMember.RelationShipDesc, |
dbo.ptApprovedCertification.TotalGrossAnnualIncome AS TotalFamilyIncomeAmount, |
dbo.ptApprovedCertification.TotalAdjustedAnnualIncome AS TotalAdjustedIncomeAmount, dbo.rmfliGetACertRentToCharge(dbo.ptApprovedCertification.PK) |
AS RentChargeAmount, |
CASE ptAConventionalCertification.FamilySubsidyStatus WHEN 'P' THEN ptAConventionalCertification.MixedFamilyTenantRent ELSE ptAConventionalCertification.TenantRent |
END AS TenantRentAmount, dbo.ptAConventionalCertification.FlatRentAmount, dbo.ptAConventionalCertification.UtilityAllowance AS UAPPaymentAmount, |
CASE ptAConventionalCertification.FamilySubsidyStatus WHEN 'P' THEN ptAConventionalCertification.MixedFamilyTTP ELSE ptApprovedCertification.TTPAmount END AS |
TTPAmount, dbo.ptAConventionalCertification.NumberInFamily, dbo.ptAConventionalCertification.NumberWithDisability AS NumberDisabledInFamily, |
dbo.pmUnit.Bedrooms AS NumberOfBedrooms, dbo.ptApprovedCertification.EffectiveDate, dbo.pmUnitResident.EndDate AS pmUnitResidentEndDate, |
dbo.pmUnitResident.StartDate AS pmUnitResidentStartDate, dbo.ptAConventionalCertification.NextReExamEffectiveDate, dbo.ptApprovedCertification.ActionType, |
CASE QualifiedDisabilityAllowance WHEN 'Yes' THEN 'Yes' ELSE 'No' END AS isDisabled, |
CASE QualifiedElderlyAllowance WHEN 'Yes' THEN 'Yes' ELSE 'No' END AS isElderly, dbo.pmPropertyType.PropertyID, dbo.pmBuilding.BuildingID, |
dbo.pmHUDProject.ProjectNumber, dbo.pmHUDProject.Description AS DevelopmentName, dbo.liUnit.UnitID, dbo.pmStructureType.Description AS UnitStructureType, |
dbo.pmUnit.UnitFloor, dbo.liBuildingEntrance.EntranceNumber AS UnitEntranceNumber, dbo.pmUnit.PrimaryStreet AS UnitPrimaryStreet, |
dbo.pmUnit.SecondaryStreet AS UnitSecondaryStreet, dbo.pmUnit.Suite AS UnitSuite, ISNULL(dbo.osCity.City, '') AS UnitCity, dbo.osCity.State AS UnitState, |
dbo.osCompanyWorker.FullName AS CompanyWorkerFullName, dbo.ptAMember.CommunityServiceHUDCode, dbo.ptAMember.CommunityServiceDesc, |
dbo.osAddress.PrimaryStreet, dbo.osAddress.SecondaryStreet, dbo.osAddress.Suite, dbo.osCity.City, dbo.osCity.State, dbo.osCity.Zip, |
(SELECT TOP (1) dbo.osCommunication.Information |
FROM dbo.osCommunication INNER JOIN |
dbo.osCommunicationType ON dbo.osCommunication.fkosCommunicationType = dbo.osCommunicationType.PK |
WHERE (dbo.osCommunication.fkosEntity = dbo.osEntity.PK) AND (dbo.osCommunication.fkosContact IS NULL) AND |
(dbo.osCommunicationType.Description = 'Phone')) AS Phone |
FROM dbo.osEntity INNER JOIN |
dbo.osParticipant ON dbo.osEntity.PK = dbo.osParticipant.fkosEntity INNER JOIN |
dbo.osParticipantAddressLink ON dbo.osParticipantAddressLink.fkosParticipant = dbo.osParticipant.PK INNER JOIN |
dbo.osAddress ON dbo.osParticipantAddressLink.fkosAddress = dbo.osAddress.PK INNER JOIN |
dbo.osCompanyModule ON dbo.osParticipant.fkosCompanyModule = dbo.osCompanyModule.PK INNER JOIN |
dbo.ptApprovedCertification ON dbo.fnGetParticipantActiveCert(dbo.osParticipant.PK, GETDATE(), NULL) = dbo.ptApprovedCertification.PK INNER JOIN |
dbo.ptAConventionalCertification ON dbo.ptApprovedCertification.PK = dbo.ptAConventionalCertification.fkptACertification INNER JOIN |
dbo.ptAMember ON dbo.ptApprovedCertification.PK = dbo.ptAMember.fkptACertification LEFT OUTER JOIN |
dbo.osParticipantCompanyWorker ON dbo.osParticipant.PK = dbo.osParticipantCompanyWorker.fkosParticipant AND |
dbo.osParticipantCompanyWorker.IsPrimaryWorker = 'Yes' LEFT OUTER JOIN |
dbo.osCompanyWorker ON dbo.osParticipantCompanyWorker.fkosCompanyWorker = dbo.osCompanyWorker.PK LEFT OUTER JOIN |
dbo.pmUnit ON dbo.ptApprovedCertification.lkpmUnit = dbo.pmUnit.PK LEFT OUTER JOIN |
dbo.pmBuilding ON dbo.pmUnit.fkpmBuilding = dbo.pmBuilding.PK LEFT OUTER JOIN |
dbo.pmProperty ON dbo.pmBuilding.fkpmProperty = dbo.pmProperty.PK LEFT OUTER JOIN |
dbo.pmPropertyType ON dbo.pmProperty.PK = dbo.pmPropertyType.fkpmProperty LEFT OUTER JOIN |
dbo.pmUnitResident ON dbo.pmUnitResident.fkosParticipant = dbo.osParticipant.PK AND dbo.pmUnitResident.PK IN |
(SELECT UnitResidentPK |
FROM dbo.rmvCurrentpmUnitResidents) LEFT OUTER JOIN |
dbo.liUnit ON dbo.liUnit.fkpmUnit = dbo.pmUnit.PK LEFT OUTER JOIN |
dbo.pmHUDProject ON dbo.liUnit.fkpmHUDProject = dbo.pmHUDProject.PK INNER JOIN |
dbo.osCity ON dbo.osAddress.fkosCity = dbo.osCity.PK INNER JOIN |
dbo.pmStructureType ON dbo.pmUnit.fkpmStructureType = dbo.pmStructureType.PK INNER JOIN |
dbo.liBuildingEntrance ON dbo.liUnit.fkliBuildingEntrance = dbo.liBuildingEntrance.PK INNER JOIN |
dbo.osCommunication AS osCommunication_1 ON dbo.osEntity.PK = osCommunication_1.fkosEntity INNER JOIN |
dbo.osCommunicationType AS osCommunicationType_1 ON osCommunication_1.fkosCommunicationType = osCommunicationType_1.PK |
WHERE (dbo.osParticipant.IsActive = 'Yes') |
Also I didn't write this TSQL so please don't judge me by it! ;)