Join in linq result not show according me

Question

0.00/5 (No votes)

See more:

Dear Sir

I m working on linq my query is

C#

var query = (from c in Categories

             join s in Subcategories on c.Id equals s.Category_id

             orderby c.Id

             select new
             {
                 cateid= c.Id,
                 catename= c.Name,
                 subcateid= s.Id,
                 subcatename= s.Name,

             }).Distinct().ToList();

     query.Dump();

my result is

cateid  catename                    subcateid   subcatename
1	Modular Office Furniture	40	45mm Panel Base System
1	Modular Office Furniture	41	60mm Panel Base System
1	Modular Office Furniture	42	75mm Tile Base System

But i want that Modular Office Furniture and id 1 is not repeat

cateid  catename                    subcateid   subcatename
1	Modular Office Furniture	40	45mm Panel Base System
       null                            	41	60mm Panel Base System
       null                             42	75mm Tile Base System

[edit]Code block added - OriginalGriff[/edit]

Posted 23-Feb-15 23:02pm

Member 8550896

Updated 23-Feb-15 23:18pm

OriginalGriff

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2015-02-24T03:05:00

The result you're getting is what is expected. Database engines return results like that all the time. You cannot avoid getting the repeated parts of the records.

The only way you can get a result anything like you want is if your query returns each record or what you don't want repeated, and then there is a child collection of your sub records.

....select new 
    {
        categoryId = c.Id,
        categoryName = c.Name,
        subcategories = (from s in Subcategories
                         where s.Category_id = c.Id
                         select new
                         {
                             subCategoryId = s.Id,
                             subCategoryName = s.Name
                         })
    }).ToList();

This will result in a collection of subcategories in each category record.

Maciej Los · Answer 2 · 2015-02-24T10:34:00

Another way is to use query like this:

C#

var qry = from c in Categories join
        s in Subcategories.Select((a,x) => new{cateid = a.cateid, index=x+1, subcateid = a.subcateid, subcatename = a.subcatename})
        on c.cateid equals s.cateid
        select new
        {
            cateid = s.index==1 ? c.cateid.ToString() : string.Empty,
            catename = s.index==1 ? c.catename : string.Empty,
            subcateid = s.subcateid,
            subcatename = s.subcatename
        };

Result:

cateid    catename                 subcateid    subcatename
1         Modular Office Furniture 40           45mm Panel Base System 
                                   41           60mm Panel Base System 
                                   42           75mm Panel Base System

Complete sample code for LinqPad[^]:

C#

void Main()
{

	List<category>> Categories = new List<category>
	{
		new category(1, "Modular Office Furniture")
	};
	
	List<subcategory> Subcategories = new List<subcategory>
	{
		new subcategory(40, 1, "45mm Panel Base System"),
		new subcategory(41, 1, "60mm Panel Base System"),
		new subcategory(42, 1, "75mm Panel Base System")
	};
	
	var qry = from c in Categories join
			s in Subcategories.Select((a,x) => new{cateid = a.cateid, index=x+1, subcateid = a.subcateid, subcatename = a.subcatename})
			on c.cateid equals s.cateid
			select new
			{
				cateid = s.index==1 ? c.cateid.ToString() : string.Empty, 
				catename = s.index==1 ? c.catename : string.Empty,
				subcateid = s.subcateid,
				subcatename = s.subcatename
			}; 
		
	qry.Dump();

}

// Define other methods and classes here
class category
{
	private int cid = 0;
	private string cn = string.Empty;

	public category(int _cid, string _cn)
	{
		cid = _cid;
		cn = _cn;
	}
	
	public int cateid
	{
		get{return cid;}
		set{cid = value;}
	}
	
	public string catename
	{
		get{return cn;}
		set{cn = value;}
	}
	

}

class subcategory
{
	private string scn = string.Empty;
	private int scid = 0;
	private int cid = 0;
	
	public subcategory(int _scid, int _cid, string _scn)
	{
		scid = _scid;
		cid = _cid;
		scn = _scn;
	}
	
	public int cateid
	{
		get{return cid;}
		set{cid = value;}
	}

	public int subcateid
	{
		get{return scid;}
		set{scid = value;}
	}
	
	public string subcatename
	{
		get{return scn;}
		set{scn = value;}
	}

}

Join in linq result not show according me

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0