This is a migrated thread and some comments may be shown as answers.

TSQL to Linq converter

5 Answers 140 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Garry
Top achievements
Rank 1
Garry asked on 17 May 2010, 03:47 PM
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!)

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 INTAS 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 NULLAND   
                                                   (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! ;)

5 Answers, 1 is accepted

Sort by
0
Zoran
Telerik team
answered on 18 May 2010, 08:45 AM
Hello Garry Clark,

 Unfortunately, we are not aware of such toll at the moment as well. However isn't it possible for you to reverse engineer this view to a class and then just do a Linq select on it? Isn't this view available in the reverse engineering wizard or the Visual Designer (I an not sure which approach are you using). Views are in most cases available to be reverse engineered by OpenAccess and mapped to a class. Then you could be able to execute a query like the following:

from t in scope.Extent<MyView>() select t;

Greetings,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Garry
Top achievements
Rank 1
answered on 18 May 2010, 12:36 PM
Zoran,
Thank you for your response and yes it is possible to Reversemap this view, but I have already reversed mapped all the tables that are contained in this view and really don't want to start reverse mapping views. Also the view in this case is a custom one that was done in house awhile ago. The project I am working on has the potential of being used by many other companies across the nation using the software package that utilizes this database. The problem is that they would not have these custom views and I would rather not try and distribute them along with my application. I am trying to use just the base tables so that no customization is done to thier database with the exception of some extension tables.

I guess I'll just have to put on my learning cap and enhance my weak Linq skills. It's not a bad thing it will just add a little more time to the project.

Thanks as always!
0
Zoran
Telerik team
answered on 18 May 2010, 02:09 PM
Hi Garry Clark,

 I understand your concerns but I really think you could double-think your decision on this one. Reverse mapping views is nothing different then reverse mapping any of the tables of your database. And I am not sure about the problems that you expect on the client side. I guess your project will be deployed with the database together and if the view is part of the database there should not be any problems with using the view directly in your project. Another argument could be that this is really a query that is hard to be re-written in Linq and especially translated back to SQL as it contains some inner selects and left outer joins which are really tricky t be processed in Linq queries. It could mean that not all of the query will be processed on the SQL server side but some of it may be processed on the client as well. By reverse mapping the view, you will be sure to always get this concrete complex SQL executed and if you use Linq there is a small chance to get exactly the same SQL against your database server. 

Best wishes,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Garry
Top achievements
Rank 1
answered on 18 May 2010, 02:18 PM
Zoran,
Thanks again for your response and I am not disagreeing with you, but as I said in my previous post, maybe not clearly though, is that this paticular view I'm working with currently will not exsist in any other database than the one I am working on now as the customer did this one themselves.

Also the database will not be distirbuted along with this project as this is just really an extension of an already in place system. Maybe I am not following you, but my understanding is that if I develop classes off of a view that has the possibility of not exsisting in the database then OpenAccess will fail if it does not see that view. Is that not correct? Or is OpenAccess smart enough to know the tables that made up that view and then compensate somehow?

Anyhow I do appreciate your thoughts on this matter!
0
Zoran
Telerik team
answered on 18 May 2010, 03:17 PM
Hi Garry Clark,

Indeed, the situation is much more clear to me after your last post. If you would like to have the view at every customer with the possibility of not having the view in the respective customers database, then Linq is really your only opportunity. OpenAccess will work correctly only if the metadata for all the tables and views that have been reverse mapped is always consistent. So, you are correctly omitting this view from mapping since your case is exactly the opposite.

Sincerely yours,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
Tags
LINQ (LINQ specific questions)
Asked by
Garry
Top achievements
Rank 1
Answers by
Zoran
Telerik team
Garry
Top achievements
Rank 1
Share this question
or