Query LINQ Mapping

2 posts, 0 answers
  1. Maher
    Maher avatar
    11 posts
    Member since:
    Jul 2012

    Posted 03 Sep 2012 Link to this post

    I would like to map this oracle sql query to LINQ for my silverlight application:

    SELECT DISTINCT (DOS.NODOSSIER) AS DOSSIER,
                    DOS.CTE AS CTE,
                    DOS.SIGCOR AS MED,
                    TRIM(MAL.NOM) AS NOM,
                    TRIM(MAL.NOMJF) AS NOMJF,
                    TRIM(TRIM(MAL.PRENOM) || ' ' || TRIM(MAL.PRENOM2)) AS PRENOM,
                    MAL.SEXE AS SEXE,
                    MAL.DATNAI AS DATENAI,
                    TRUNC((MONTHS_BETWEEN(SYSDATE, TO_DATE(MAL.DATNAI, 'yyyymmdd'))) / 12) AS AGE,
                    DOS.PASSDOS AS PASSDOS,
                    MAL.DATDC AS DATEDC,
                    GL.UNITE AS UNITE,
                    GL.NUMETAGE AS ETAGE,
                    GL.NUMCHAMBRE AS CHAMBRE,
                    GL.PLACELIT AS LIT,
                    GL.NUMTEL AS TEL,
                    TO_CHAR(DATEDEB_MOUV, 'dd/mm/yyyy hh24:mm') AS DEBMOUV,
                    TO_CHAR(DATEFIN_MOUV, 'dd/mm/yyyy hh24:mm') AS FINMOUV,
                    TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mm') AS DATESYS
                    
      FROM IM_MALAD MAL, IM_DOSSIER DOS
      
      LEFT JOIN GLOM_VIEW_SEJOUR GL ON GL.NODOSSIER = DOS.NODOSSIER AND (GL.FLAG_MOUV IS NULL OR GL.FLAG_MOUV = ' ')
    AND (GL.FLAGPROVIS_MOUV != 'P' OR GL.FLAGPROVIS_MOUV IS NULL)
    AND (GL.FLAG_OCC IS NULL OR GL.FLAG_OCC = ' ')
    AND (GL.FLAGPROVIS_OCC != 'P' OR GL.FLAGPROVIS_OCC IS NULL)
      
     WHERE MAL.NODOSSIER = DOS.NODOSSIER   AND MAL.NOM LIKE 'BERNIER%'   AND MAL.SEXE = 'M'
     
     ORDER BY NOM, PRENOM, DOS.NODOSSIER
  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 06 Sep 2012 Link to this post

    Hi Maher,

     This is a complex query using a lot of server-side functions and querying from two tables without a join specified and then joining with a third table. It should be generally possible to reproduce the desired result with a LINQ query, but I would prefer another approach which I find more straight-forward and easier to implement. This query, looks like the perfect candidate for a database View, which you can create on your server. OpenAccess is able to map views to entities so you would get an object with the name of the view and there will be an IQueryable end-point on the context. So imagine your view is called "CustomView":

    EntitiesModel context = new EntitiesModel();
    IList<CustomView> result = context.CustomViews.ToList();

    The code above will execute the exact same query you have in the database and materialize it to an appropriate object.

    Regards,
    Zoran
    the Telerik team
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  3. DevCraft banner
Back to Top