Databases
StackOverflow2013
Postsdbo
POlinq GroupBy with Where
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POlinq GroupBy with Where
    primarykey
    data
    text
    <p>I have entries in table codelistvalueview of this type:</p> <p>I want to group by namespace and maybe then by tablename and find out all the entries that only occur in both the namespaces (UPD/REFDAT) and then list then ones that occur in UDP so they can be deleted.</p> <pre><code>namespace tableid tablename count UDP 1C06F2EF-5371-4A3F-A07C-226DB7242053 WeldingProcedureSpecification 34 REFDAT 42D225CA-A96B-4806-9C5C-86D2B3B3AFEE WeldingProcedureSpecification 2 REFDAT EA0F846C-59B4-4F6D-91D1-B00698C98349 WeldClass 5 REFDAT E8516DFC-9980-4CBC-B62C-D2C11618424E WasherType 14 </code></pre> <p>In the case above, I need the tableid from the 1st row only since that occurs in both 1 &amp; 2 row. The value I need is, the tableid from the row with UDP as the namespace (1st row in this case)</p> <p>I would think this (below) would get me what I want but I'm getting some null rows in between ??</p> <pre><code>var grp = CodelistValueView.Where(x=&gt;x.Namespace=="UDP" || x.Namespace=="REFDAT") .GroupBy(x=&gt;new {x.Namespace, x.TableID, x.TableName}, x=&gt;new {x.Namespace, x.TableID, x.TableName, x.ShortStringValue}) .OrderByDescending(g=&gt;g.Key.TableName) .Select(g=&gt;g.Where(x=&gt;x.Namespace=="UDP").First()); grp.Dump(); </code></pre> <p>This is what I'm seeing...</p> <pre><code>null null UDP 1c06f2ef-5371-4a3f-a07c-226db7242053 WeldingProcedureSpecification GTAW, SA-789 null </code></pre> <p>Any thoughts ??</p> <p>I finally came up with this that seems to work...not sure if this is the best way to do this.</p> <pre><code>var grp = CodelistValueView.Where(x=&gt;x.Namespace=="UDP" || x.Namespace=="REFDAT") .Select(x=&gt;new {NS=x.Namespace, Tablename=x.TableName, TableId=x.TableID}) .GroupBy(g=&gt; new {g.NS, g.Tablename, g.TableId}, (g,x)=&gt;g) .GroupBy(x=&gt;x.Tablename, x=&gt;x) .Where(x=&gt;x.Count() &gt; 1) .Select(x=&gt;x.Where(a=&gt;a.NS=="UDP").First()) .OrderBy(x=&gt;x.Tablename); </code></pre> <p>And I get this:</p> <pre><code>NS Tablename TableId UDP ValveFlowPattern 64bd5be2-0ddb-495a-a0db-28476ebe858d UDP ValveOperatorPartDataBasis dcdb1f66-83f1-4738-8587-49a72c63801d UDP ValvePortOption 99b1797c-4712-410a-8578-d4a6a01e8968 UDP WeldingProcedurePractice 682bcc0b-db7a-4b10-80ba-1f969b96abfe UDP WeldingProcedureSpecification 1c06f2ef-5371-4a3f-a07c-226db7242053 </code></pre> <p>thanks Sunit</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSunit
    1. USSunit
    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. COSo you're asking for two queries here?
      singulars
      1. POlinq GroupBy with Where
      1. USStu
    2. COI'm not sure. I need to list the ones that occur in UPD if and only if they also occur in REFDAT.
      singulars
      1. POlinq GroupBy with Where
      1. USSunit
    3. COCould you be more clear on the question please ? Do you want to find records that are in both the namespaces or that are there in UDP namespace only ? sorry I didn't understand your question properly
      singulars
      1. POlinq GroupBy with Where
      1. USMahesh Velaga
 

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