How to Join LINQ to another LINQ

Question

i have a query like this

WITH CTE_KELOMPOKINFORMASI (KelompokInformasi, XBRLItem_ItemId) 
AS (
SELECT a.Id AS KelompokInformasi, c.XBRLItem_ItemId
FROM XBRLNamespaces a INNER JOIN XBRLHypercubes b 
ON a.XBRLView_ViewId = b.XBRLView_ViewId 
INNER JOIN XBRLHypercubeDimensionItems c 
ON b.XBRLHypercubeId = c.XBRLHypercube_XBRLHypercubeId 
WHERE a.Id like '%KBIK_AAKL%')

SELECT f.KelompokInformasi, e.Name AS DimensionName, c.Id AS Domain, 
d.Text AS Description FROM [dbo].[XBRLDefinitionRoleDomainItems] a
INNER JOIN [dbo].[XBRLDefinitionRoleDimensionItems] b
ON a.XBRLDefinitionRole_DefinitionRoleId = b.XBRLDefinitionRole_DefinitionRoleId 
INNER JOIN XBRLItems c ON a.XBRLItem_ItemId = c.ItemId 
INNER JOIN XBRLLabels d 
ON a.XBRLItem_ItemId = d.XBRLItem_ItemId 
INNER JOIN XBRLItems e 
ON b.XBRLItem_ItemId=e.ItemId 
INNER JOIN CTE_KELOMPOKINFORMASI f 
ON b.XBRLItem_ItemId=f.XBRLItem_ItemId 
WHERE b.XBRLItem_ItemId=f.XBRLItem_ItemId

i want to move this sql query to linq, i realized that CTE is impossible in LINQ. So i divide into 2 parts. First i create a var like this:

var KelompokInformasi = from x in ent.XBRLNamespaces
                                    join y in ent.XBRLHypercubes on x.XBRLView_ViewId equals y.XBRLView_ViewId
                                    join z in ent.XBRLHypercubeDimensionItems on y.XBRLHypercubeId equals z.XBRLHypercube_XBRLHypercubeId
                                    where x.Id.Contains("KBIK")
                                    select new
                                    {
                                        x.Id,
                                        y.XBRLItem_ItemId
                                    };

and in second part i create:

_list = (from a in ent.XBRLDefinitionRoleDomainItems
                     join b in ent.XBRLDefinitionRoleDimensionItems on a.XBRLDefinitionRole_DefinitionRoleId equals b.XBRLDefinitionRole_DefinitionRoleId
                     join c in ent.XBRLItems on a.XBRLItem_ItemId equals c.ItemId
                     join d in ent.XBRLLabels on a.XBRLItem_ItemId equals d.XBRLItem_ItemId
                     join e in ent.XBRLItems on b.XBRLItem_ItemId equals e.ItemId
                     join f in KelompokInformasi on b.XBRLItem_ItemId equals (int)f.XBRLItem_ItemId
                     where (b.XBRLItem_ItemId == (int)f.XBRLItem_ItemId)
                     select new MappingDomainRepository
                     {
                         KI = f.Id,
                         Dimension = e.Name,
                         Domain = c.Id,
                         Description = d.Text
                     }).ToList();

Where _list is from List<MappingDomainRepository> _list = new List<MappingDomainRepository>();

in my code above, i want to join my _list to var KelompokInformasi. In var kelompokInformasi I've got 47 rows but in _list I've got 0 data return.

What's wrong in my code? is it possible to join my _list to var kelompokInformasi?

Look at a tool for ceonversion :) http://stackoverflow.com/questions/296972/sql-to-linq-tool — lordkain, Jan 25 '14 at 10:33

score 0 · Accepted Answer · answered Jan 25 '14 at 14:45

You need to change the second part to:

var other = (from a in ent.XBRLDefinitionRoleDomainItems
             join b in ent.XBRLDefinitionRoleDimensionItems on a.XBRLDefinitionRole_DefinitionRoleId equals b.XBRLDefinitionRole_DefinitionRoleId
             join c in ent.XBRLItems on a.XBRLItem_ItemId equals c.ItemId
             join d in ent.XBRLLabels on a.XBRLItem_ItemId equals d.XBRLItem_ItemId
             join e in ent.XBRLItems on b.XBRLItem_ItemId equals e.ItemId
             join f in KelompokInformasi on b.XBRLItem_ItemId equals (int)f.XBRLItem_ItemId
             where (b.XBRLItem_ItemId == (int)f.XBRLItem_ItemId)
             select new MappingDomainRepository
             {
                 KI = f.Id,
                 Dimension = e.Name,
                 Domain = c.Id,
                 Description = d.Text,
                 XBRLItem_ItemId  = a.XBRLItem_ItemId 
            };

...which adds in the XBRLItem_ItemId which use to join to the CTE.

Then join the two together. We have other (above) and KelompokInformasi from the CTE:

var result = from x in KelompokInformasi 
             join o in other on x.XBRLItem_ItemId equals o.XBRLItem_ItemId
             select new {KelompokInformasi = o.KelompokInformasi, 
                         DimensionName = o.Name, 
                         Domain = o.Id,
                         Description = o.Text
                        };

..which appears to be the columns you exentually select.

Thanks for answered my question. I've changed my second part like the code above, but in var other i'm still get 0 result. When i look var other in Quick Watch I've got this message "Empty "Enumeration yielded no results" string" — bagusofterror, Jan 25 '14 at 17:15
OK. It could be that that is the correct value to return. All I did was take your statement and add a.XBRLItem_ItemId to the select. I's suggest checking against the SQL, and also checking that the logic of the joins is correct (I can't do that: I have no idea what your data is or means!) — simon at rcl, Jan 25 '14 at 17:55
I will correct again my join query .. Thanks for the info, it helps very much Simon :) — bagusofterror, Jan 26 '14 at 03:03

How to Join LINQ to another LINQ

1 Answers1