TSQL to Linq converter

6 posts, 0 answers
  1. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 17 May 2010 Link to this post

    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! ;)
  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 18 May 2010 Link to this post

    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.
  3. DevCraft banner
  4. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 18 May 2010 Link to this post

    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!
  5. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 18 May 2010 Link to this post

    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.
  6. Garry
    Garry avatar
    205 posts
    Member since:
    Nov 2010

    Posted 18 May 2010 Link to this post

    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!
  7. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 18 May 2010 Link to this post

    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.
Back to Top
DevCraft banner