Databases
StackOverflow2013
Postsdbo
POMassive SQL query requires conversion to LINQ
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMassive SQL query requires conversion to LINQ
    primarykey
    data
    text
    <p>I have an SQL query which I need to convert to LINQ. have spent a whole day on it, didn't have much luck tough. Here's the query</p> <pre><code>SELECT PC.PRSCLAIMID, date_format(PC.DATEOFCLAIM, '%d/%m/%Y') as DateofClaim, PC.PRSREFNO, PC.FKARTISTID, PC.FKSETLISTINFOID, PC.STATUS, PC.FROMDNN, PC.EXPORTTYPE, PC.FK_CATEGORYID, A.LOGINNAME, A.BANDNAME, SMCATEGORIESTYPE.CATEGORYTYPE, Count(SMLIVECLAIMS.LIVECLAIMSID) AS GIGCOUNT, SMCATEGORIES.ID AS PROCESSEDID, SMCATEGORIES.CATEGORY, Ifnull(BC.FILENAME, '') AS BULKCLAIM_NAME FROM SMARTISTDETAILS AS A INNER JOIN SMPRSCLAIMS AS PC ON A.ARTISTID = PC.FKARTISTID INNER JOIN SMCATEGORIES ON PC.FK_CATEGORYID = SMCATEGORIES.ID INNER JOIN SMCATEGORIESTYPE ON SMCATEGORIES.FK_CATEGORYTYPEID = SMCATEGORIESTYPE.ID INNER JOIN SMPRSLIVECLAIMLINK ON PC.PRSCLAIMID = SMPRSLIVECLAIMLINK.FKPRSCLAIMID INNER JOIN SMLIVECLAIMS ON SMPRSLIVECLAIMLINK.FKLIVECLAIMID = SMLIVECLAIMS.LIVECLAIMSID LEFT OUTER JOIN BULKCLAIMDETAIL BCD ON BCD.FKPRSCLAIMID = PC.PRSCLAIMID LEFT OUTER JOIN BULKCLAIM BC ON BC.ID_BULKCLAIM = BCD.ID_BULKCLAIM WHERE ( PC.EXPORTTYPE &gt; 0 ) AND ( A.DBID = 1 ) AND ( CASE WHEN PC.EXPORTTYPE = 1 AND ( SMLIVECLAIMS.GIGDATE &lt; Date_add(Now(), INTERVAL -1 YEAR) ) THEN 0 ELSE 1 END = 1 ) GROUP BY PC.PRSCLAIMID ORDER BY PC.DATEOFCLAIM DESC </code></pre> <p>what I did was the following conversion</p> <pre><code> from e in (from artistData in dbContext.smartistdetails where artistData.DbId == 1 // SessionFacade.DBId join smprsclaimsData in dbContext.smprsclaims on artistData.ArtistID equals smprsclaimsData.fkArtistID join smCategoriesData in dbContext.smcategories on smprsclaimsData.FK_CategoryID equals smCategoriesData.Id join smCategoriesTypeData in dbContext.smcategoriestype on smCategoriesData.fk_CategoryTypeId equals smCategoriesTypeData.Id join smPrsLiveClaimsLinkData in dbContext.smprsliveclaimlink on smprsclaimsData.PRSClaimID equals smPrsLiveClaimsLinkData.fkPRSClaimID join smLiveClaimData in dbContext.smliveclaims on smPrsLiveClaimsLinkData.fkLiveClaimID equals smLiveClaimData.LiveclaimsID join bulkClaimDetailData in dbContext.bulkclaimdetail on smprsclaimsData.PRSClaimID equals bulkClaimDetailData.fkPRSClaimID into bulkClaimDetailJoined from bulkClaimDetailJoinedData in bulkClaimDetailJoined.DefaultIfEmpty() join bulkClaimData in dbContext.bulkclaim on bulkClaimDetailJoinedData.ID_BulkClaim equals bulkClaimData.ID_BulkClaim into bulkClaimJoined from bulkClaimJoinedData in bulkClaimJoined.DefaultIfEmpty() where smprsclaimsData.ExportType &gt; 0 select new { PRSCLAIMID_Inner = smprsclaimsData.PRSClaimID, GigDate = smLiveClaimData.GigDate, DateofClaim = smprsclaimsData.DateofClaim, PRSREFNO = smprsclaimsData.PRSRefNo, FKARTISTID = smprsclaimsData.fkArtistID, FKSETLISTINFOID = smprsclaimsData.fkSetlistInfoID, STATUS = smprsclaimsData.Status, FROMDNN = smprsclaimsData.FromDNN, EXPORTTYPE = smprsclaimsData.ExportType, FK_CATEGORYID = smprsclaimsData.FK_CategoryID, LOGINNAME = artistData.LoginName, BANDNAME = artistData.BandName, CATEGORYTYPE = smCategoriesTypeData.CategoryType, LiveclaimsID = smLiveClaimData.LiveclaimsID, PROCESSEDID = smCategoriesData.Id, CATEGORY = smCategoriesData.Category, BULKCLAIM_NAME = bulkClaimJoinedData.FileName == null ? "" : bulkClaimJoinedData.FileName }) where!(e.EXPORTTYPE == 1 &amp;&amp; e.GigDate &lt; oneYearOldDate) group e by e.PRSCLAIMID_Inner into groupedData select new { PRSCLAIMID_Outer = groupedData.Key, GIGCOUNT = groupedData.Count(), DateofClaim = groupedData.Select(a = &gt; a.DateofClaim).FirstOrDefault(), PRSREFNO = groupedData.Select(a = &gt; a.PRSREFNO).FirstOrDefault(), FKARTISTID = groupedData.Select(a = &gt; a.FKARTISTID).FirstOrDefault(), FKSETLISTINFOID = groupedData.Select(a = &gt; a.FKSETLISTINFOID).FirstOrDefault(), STATUS = groupedData.Select(a = &gt; a.STATUS).FirstOrDefault(), FROMDNN = groupedData.Select(a = &gt; a.FROMDNN).FirstOrDefault(), EXPORTTYPE = groupedData.Select(a = &gt; a.EXPORTTYPE).FirstOrDefault(), FK_CATEGORYID = groupedData.Select(a = &gt; a.FK_CATEGORYID).FirstOrDefault(), LOGINNAME = groupedData.Select(a = &gt; a.LOGINNAME).FirstOrDefault(), BANDNAME = groupedData.Select(a = &gt; a.BANDNAME).FirstOrDefault(), CATEGORYTYPE = groupedData.Select(a = &gt; a.CATEGORYTYPE).FirstOrDefault(), PROCESSEDID = groupedData.Select(a = &gt; a.PROCESSEDID).FirstOrDefault(), CATEGORY = groupedData.Select(a = &gt; a.CATEGORY).FirstOrDefault(), BULKCLAIM_NAME = groupedData.Select(a = &gt; a.BULKCLAIM_NAME).FirstOrDefault() }).OrderByDescending(a = &gt; a.DateofClaim) </code></pre> <p>this conversion is working but it is too slow, then I create another conversion</p> <pre><code>from smprsclaimsData in dbContext.smprsclaims group smprsclaimsData by new { smprsclaimsData.PRSClaimID, smprsclaimsData.fkArtistID, smprsclaimsData.FK_CategoryID } into smprsclaimsGroupedData join artistData in dbContext.smartistdetails on smprsclaimsGroupedData.Key.fkArtistID equals artistData.ArtistID let loginName = artistData.LoginName let bandName = artistData.BandName join smCategoriesData in dbContext.smcategories on smprsclaimsGroupedData.Key.FK_CategoryID equals smCategoriesData.Id join smCategoriesTypeData in dbContext.smcategoriestype on smCategoriesData.fk_CategoryTypeId equals smCategoriesTypeData.Id join smPrsLiveClaimsLinkData in dbContext.smprsliveclaimlink on smprsclaimsGroupedData.Key.PRSClaimID equals smPrsLiveClaimsLinkData.fkPRSClaimID join smLiveClaimData in dbContext.smliveclaims on smPrsLiveClaimsLinkData.fkLiveClaimID equals smLiveClaimData.LiveclaimsID join bulkClaimDetailData in dbContext.bulkclaimdetail on smprsclaimsGroupedData.Key.PRSClaimID equals bulkClaimDetailData.fkPRSClaimID into bulkClaimDetailJoined from bulkClaimDetailJoinedData in bulkClaimDetailJoined.DefaultIfEmpty() join bulkClaimData in dbContext.bulkclaim on bulkClaimDetailJoinedData.ID_BulkClaim equals bulkClaimData.ID_BulkClaim into bulkClaimJoined from bulkClaimJoinedData in bulkClaimJoined.DefaultIfEmpty() where smprsclaimsGroupedData.Select(a =&gt; a.ExportType).FirstOrDefault() &gt; 0 &amp;&amp; artistData.DbId == 1 &amp;&amp; !(smprsclaimsGroupedData.Select(a =&gt; a.ExportType).FirstOrDefault() == 1 &amp;&amp; smLiveClaimData.GigDate &lt; oneYearOldDate) select new { PRSCLAIMID_Inner = 1, GigDate = smLiveClaimData.GigDate, DateofClaim = smprsclaimsGroupedData.Select(a =&gt; a.DateofClaim), PRSREFNO = smprsclaimsGroupedData.Select(a =&gt; a.PRSRefNo), FKARTISTID = smprsclaimsGroupedData.Select(a =&gt; a.fkArtistID), FKSETLISTINFOID = smprsclaimsGroupedData.Select(a =&gt; a.fkSetlistInfoID), STATUS = smprsclaimsGroupedData.Select(a =&gt; a.Status), FROMDNN = smprsclaimsGroupedData.Select(a =&gt; a.FromDNN), EXPORTTYPE = smprsclaimsGroupedData.Select(a =&gt; a.ExportType), FK_CATEGORYID = smprsclaimsGroupedData.Select(a =&gt; a.FK_CategoryID), LOGINNAME = loginName, BANDNAME = bandName, CATEGORYTYPE = smCategoriesTypeData.CategoryType, //GIGCOUNT = smprsclaimsGroupedData.Count(), PROCESSEDID = smCategoriesData.Id, CATEGORY = smCategoriesData.Category, BULKCLAIM_NAME = bulkClaimJoinedData.FileName == null ? "" : bulkClaimJoinedData.FileName, }) </code></pre> <p>well this query is not fetching the expected data, i think it is due to multiple (group by)'s. I cannot post my DB Schema as well, the question is already too long to read. Any help or any suggestions on how to get around or work around this conversion. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMoon
    1. USMoon
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhy does this need to be done? It seems to me that it'd be easier just to create the query as a view in the database and create some entity off of the view or define the query as a model directly.. Is this an effort to define everything as models in order to be able to switch the database, I might see why you might undertake the effort, but this...I tend to think it's more trouble than its worth. ORMs are good for generating code for common situations; SQL is good for querying data when ORMs aren't good enough.
      singulars
      1. POMassive SQL query requires conversion to LINQ
      1. USJayC
    2. COI'm not saying it cannot be translated to LINQ better, but if the best you can do is write something that looks a lot more complicated than the original SQL, I think the effort ought to be questioned.
      singulars
      1. POMassive SQL query requires conversion to LINQ
      1. USJayC
    3. COthe thing is I've been given a bug to solve in an existing system, the solution we made to solve is refactor the particular piece of code and in there are a particular point use LINQ instead of that query. If it was my own code, I'd've gone with a `view`
      singulars
      1. POMassive SQL query requires conversion to LINQ
      1. USMoon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload