linq GroupBy with Where

Question

I have entries in table codelistvalueview of this type:

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.

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

In the case above, I need the tableid from the 1st row only since that occurs in both 1 & 2 row. The value I need is, the tableid from the row with UDP as the namespace (1st row in this case)

I would think this (below) would get me what I want but I'm getting some null rows in between ??

var grp = CodelistValueView.Where(x=>x.Namespace=="UDP" || x.Namespace=="REFDAT")
      .GroupBy(x=>new {x.Namespace, x.TableID, x.TableName}, 
     x=>new {x.Namespace, x.TableID, x.TableName, x.ShortStringValue})
      .OrderByDescending(g=>g.Key.TableName)
      .Select(g=>g.Where(x=>x.Namespace=="UDP").First());

grp.Dump();

This is what I'm seeing...

null  
null  
UDP 1c06f2ef-5371-4a3f-a07c-226db7242053 WeldingProcedureSpecification GTAW, SA-789 
null

Any thoughts ??

I finally came up with this that seems to work...not sure if this is the best way to do this.

var grp = CodelistValueView.Where(x=>x.Namespace=="UDP" || x.Namespace=="REFDAT")
      .Select(x=>new {NS=x.Namespace, Tablename=x.TableName, TableId=x.TableID})
  .GroupBy(g=> new {g.NS, g.Tablename, g.TableId}, (g,x)=>g)
  .GroupBy(x=>x.Tablename, x=>x)
  .Where(x=>x.Count() > 1)
  .Select(x=>x.Where(a=>a.NS=="UDP").First())    
  .OrderBy(x=>x.Tablename);

And I get this:

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

thanks Sunit

Answer 1

I think this will work for you if I understand the question properly. I'm using "codelistvalueview" as the collection that has the tables data in it.

var refdatItems = codelistvalueview.Where(x=>x.@namespace == "REFDAT");
var udpItems = codelistvalueview.Where(x=>x.@namespace == "UDP");

var result = 
    from refItem in refdatItems
    join udpItem in udpItems on refItem.tablename equals udpItem.tablename
    select udpItem;

-- or --

var result =
    from ref in codelistvalueview
    join udp in codelistvalueview
        on ref.tablename = udp.tablename
    where ref.@namespace == "REFDAT" &&
          udp.@namespace == "UDP"
    select udp;

The result varable has all of the "UDP" items that also have "REFDAT" items where their table names are the same.

-- Edit --

I'm guessing from your last update that you are using LinqPad to figure out this query. Thats great, because I use it as well. I've updated the query to use the names you picked out. Try running this as a "C# Program" in LinqPad. It selects the TableID and filters out any duplicates.

void Main()
{
    var CodelistValueView = new data[] {
        new data() {TableName = "1", Namespace="UDP", TableID=1},
        new data() {TableName = "1", Namespace="REFDAT", TableID=1},
        new data() {TableName = "2", Namespace="UDP", TableID=3},
        new data() {TableName = "3", Namespace="REFDAT", TableID=4},
        new data() {TableName = "4", Namespace="UDP", TableID=1},
        new data() {TableName = "4", Namespace="REFDAT", TableID=1},
        new data() {TableName = "5", Namespace="other", TableID=5},
        new data() {TableName = "6", Namespace="UDP", TableID=2},
        new data() {TableName = "6", Namespace="REFDAT", TableID=2}
    };

    var result =
        from Ref in CodelistValueView
        join udp in CodelistValueView
        on Ref.TableName equals udp.TableName
        where Ref.Namespace == "REFDAT" &&
              udp.Namespace == "UDP"
        select udp.TableID;

    result.Distinct().Dump();
}

// Define other methods and classes here
class data
{
    public string TableName;
    public string Namespace;
    public int TableID;
}