Share via

Linq query returns only the first sequence in a list in C#

Question

Wednesday, July 18, 2018 7:57 AM

Hi,

Could anyone please tell me whats wrong with the below Linq query? It iterate only first sequence 3 times based on CatNo 1. Means, CategoryNoList is 1,2,3, the query returns 3 same rows where CatNo is 1. But it should return 3 rows for each 3 CatNo.

Note: CatName is string and CatNo is int?

using (DefaultConnection dbCon = new DefaultConnection())
            {
                List<Products> prod = new List<Products>();
                var CategoryNoList = DefaultConnection.Products.Where(p => p.CatName == "catname").Select(p => p.CatNo).ToList();

                foreach (var catno in CategoryNoList)
                { 
                    var ProdList = (from ProdRec in dbCon.Products
                                    where ProdRec.CatName == "catname" && ProdRec.CatNo == catno
                                    select ProdRec).First();
                    prod.Add(ProdList);
                }
                return View(prod.ToList());
            }

All replies (29)

Thursday, July 19, 2018 5:27 PM âś…Answered

According to the result, we can see your data are wrong

var ProdList = (from ProdRec in dbCon.Products 
                   where ProdRec.CatName == "catname" && ProdRec.CatNo == catno 
              select ProdRec);

In the above code , we didn't use .First

And we fetch all records for each catno

Now if you see the result . for each catno it returns name 1 without first() function

Also if you try my first sample with the hard code data, you can see it works well, then we can understandand that your data have problem

List<Products> list = new List<Products>()
        {
            new Products(){CatNo =  1 , CatName="name 1" , Product_Qty ="100" ,  } ,
            new Products(){CatNo =  2 , CatName="name 1" , Product_Qty ="200" ,  } ,
            new Products(){CatNo =  3 , CatName="name 1" , Product_Qty ="300" ,  } ,
            new Products(){CatNo =  1 , CatName="name 2" , Product_Qty ="400" ,  } ,
            new Products(){CatNo =  2 , CatName="name 2" , Product_Qty ="200" ,  }

        };
        List<Products> prod = new List<Products>();
            var CategoryNoList = list.Where(p => p.CatName == "name 1").Select(p => p.CatNo).ToList();

            foreach (var catno in CategoryNoList)
            {
                var ProdList = (from ProdRec in list
                    where ProdRec.CatName == "name 1" && ProdRec.CatNo == catno
                    select ProdRec).First();
                prod.Add(ProdList);
            }

foreach (var item in prod)
            {
            Response.Write(item.CatName + " " + item.CatNo +"<br />");
            }

Wednesday, July 18, 2018 8:32 AM

hi

I didn't get it clearly, can you describe the problem more?

<g class="gr_ gr_71 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="71" data-gr-id="71">also</g> you said >> it should return 3 rows for each 3 CatNo.

 var ProdList = (from ProdRec in dbCon.Products
                                    where ProdRec.CatName == "catname" && ProdRec.CatNo == catno
                                    select ProdRec).First();

if you want to get All <g data-gr-id="310" id="310" class="gr_ gr_310 gr-alert gr_spell ContextualSpelling ins-del multiReplace">recoredd</g>, please remove <g class="gr_ gr_277 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="277" data-gr-id="277"><g class="gr_ gr_271 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="271" data-gr-id="271">the</g> .</g>First() 

.First() just return first value

Wednesday, July 18, 2018 8:43 AM

Thanks Vahid for your response.

.First() will return the first row based on the catno. i.e. if catno is 1, it will returns the record where catno is 1, and the same for 2 and 3. Each first record is being added in the list 

prod.Add(ProdList);

Isn't it correct?

Wednesday, July 18, 2018 8:47 AM

that's right, what's the problem now?

Wednesday, July 18, 2018 8:59 AM

In the Product list I have multiple records with Category No.

ProductQty CatName CatNo
100 Name 1 1
200 Name 1 2
300 Name 1 3
400 Name 2 1
200 Name 2 2

If query rows where Product Name is "Name 1" it should return 3 rows for Category No 1, 2 and 3. But the query returns the first rows where Category No is 1 for three times like below-

ProductQty CatName CatNo
100 Name 1 1
100 Name 1 1
100 Name 1 1

Wednesday, July 18, 2018 9:17 AM

<g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="10" data-gr-id="10">no ,</g> your code is <g class="gr_ gr_11 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="11" data-gr-id="11">correct ,</g><g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">becuse</g> it <g class="gr_ gr_6 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="6" data-gr-id="6">return</g> 3 row with  CatNo 1,2,3

are you sure your result was 1,1,1

I created a  sample that <g class="gr_ gr_8 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="8" data-gr-id="8">show</g> with <g class="gr_ gr_7 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="7" data-gr-id="7">hardoce</g> value, please try it

List<Products> list = new List<Products>()
        {
            new Products(){CatNo =  1 , CatName="name 1" , Product_Qty ="100" ,  } ,
            new Products(){CatNo =  2 , CatName="name 1" , Product_Qty ="200" ,  } ,
            new Products(){CatNo =  3 , CatName="name 1" , Product_Qty ="300" ,  } ,
            new Products(){CatNo =  1 , CatName="name 2" , Product_Qty ="400" ,  } ,
            new Products(){CatNo =  2 , CatName="name 2" , Product_Qty ="200" ,  }

        };
        List<Products> prod = new List<Products>();
            var CategoryNoList = list.Where(p => p.CatName == "name 1").Select(p => p.CatNo).ToList();

            foreach (var catno in CategoryNoList)
            {
                var ProdList = (from ProdRec in list
                    where ProdRec.CatName == "name 1" && ProdRec.CatNo == catno
                    select ProdRec).First();
                prod.Add(ProdList);
            }

foreach (var item in prod)
            {
            Response.Write(item.CatName + " " + item.CatNo +"<br />");
            }

 result

name 1 1
name 1 2
name 1 3

Wednesday, July 18, 2018 9:30 AM

Am I doing anything different? I am sure it returns the same row, I tried it in different ways multiple times. Do you see anything that might be the cause?

One thing, when I debug my code I see in this code-

var ProdList = (from ProdRec in list
                    where ProdRec.CatName == "name 1" && ProdRec.CatNo == catno
                    select ProdRec).First();

the catno is correct but returns the first row. i.e. it shows catno 2 or 3 in the condition but returns the first row.  

Wednesday, July 18, 2018 9:35 AM

did you try my sample with the hard code values?

Wednesday, July 18, 2018 9:36 AM

You are right, I will try your code and let you know.

Wednesday, July 18, 2018 10:11 AM

Your sample works! I have no idea what is the problem with mine.

Wednesday, July 18, 2018 10:18 AM

if the code that you put here, is your main code, this is correct and your values are not valid in your database

if you put the piece of code from your main code, maybe you have a mistake there.

I suggest creating a new Table of Products or clear all of the records in the Products Table and insert 3 or 4 valid records like tour the last example, and try it again

Wednesday, July 18, 2018 10:55 AM

Yes, the code I put here is copied from my application. I checked again and everything is Ok.

The source I am pulling data is basically a view from another table. This view is synced with the main database table.

I think, this shouldn't be an issue as I am only pulling data.

Wednesday, July 18, 2018 11:07 AM

I suggest for check your data , you can use a foeach and response write for see them

change below code

foreach (var catno in CategoryNoList)
                { 
                    var ProdList = (from ProdRec in dbCon.Products
                                    where ProdRec.CatName == "catname" && ProdRec.CatNo == catno
                                    select ProdRec).First();
                    prod.Add(ProdList);
                }

to

oreach (var catno in CategoryNoList)
            {
                var ProdList = (from ProdRec in dbCon.Products 
                   where ProdRec.CatName == "catname" && ProdRec.CatNo == catno 
              select ProdRec);

                foreach (var item in ProdList)
                {
                    Response.Write(item.CatName + " " + item.CatNo + "<br />");
                }
                prod.Add(ProdList.First());
            }

now you can validate  your values

Wednesday, July 18, 2018 11:25 AM

I just tested it. Inthe below line the catno is correct-

 where ProdRec.CatName == "catname" && ProdRec.CatNo == catno

But in the below line the catno is always 1

Response.Write(item.CatName + " " + item.CatNo + "<br />");

Wednesday, July 18, 2018 11:31 AM

But in the below line the <g class="gr_ gr_6 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="6" data-gr-id="6">catno</g> is always 1

Response.Write(item.CatName + " " + item.CatNo + "<br />");

According to your test, your <g class="gr_ gr_11 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="11" data-gr-id="11">all  </g><g class="gr_ gr_11 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="11" data-gr-id="11">item</g>.CatNoare   1

because we removed First and it shows all records before <g class="gr_ gr_12 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="12" data-gr-id="12"><g class="gr_ gr_8 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins doubleReplace replaceWithoutSep" id="8" data-gr-id="8"><g class="gr_ gr_9 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="9" data-gr-id="9">call</g></g> .</g>First()

Thursday, July 19, 2018 8:33 AM

Hi pointtoshare,

Actually, I can't reproduce your issue when I make a demo according to your code.It works fine.

So I guess there are something wrong with the data source form db.

  var ProdList = (from ProdRec in dbCon.Products
                                    where ProdRec.CatName == "catname" && ProdRec.CatNo == catno
                                    select ProdRec).First();

Why you use First() here.

(from ProdRec in dbCon.Products
                                    where ProdRec.CatName == "catname" && ProdRec.CatNo == catno
                                    select ProdRec)

Does the result of the code above has multiple lines? 

var CategoryNoList = DefaultConnection.Products.Where(p => p.CatName == "catname").Select(p => p.CatNo).ToList();

And the result of this line above has multiple lines too?

I suggest you could add .Distinct() to delete duplication,like:

CategoryNoList.Distinct();

In addition,I think your code is complex.

In my opinion,we can use linq to complete task easily.

For example:

List<Products> prod = dbCon.Products.Where(a => a.CatName == "catname").ToList();

Best Regards.

Yuki Tao

Thursday, July 19, 2018 9:27 AM

Hi Yuki,

i used .First() because, catno is multiple here and it should returns every first line of every catno that is fine. I have already tried that you have suggested but the result is same. Probably, its happening for the data structure in the database view?

Thanks.

Thursday, July 19, 2018 10:04 AM

Hi pointtoshare,

i used .First() because, catno is multiple here and it should returns every first line of every catno that is fine. I have already tried that you have suggested but the result is same. Probably, its happening for the data structure in the database view?

It is possible.

Because I can't reproduce your issue.My demo works fine.

I suggest you could add some breakpoints on your code and check the result.

Or,you could post a part of your table data.

I can make a demo according to these data.

Best Regards.

Yuki Tao

Thursday, July 19, 2018 10:31 AM

Hi Yuki,

As I mentioned in my previous posts, I have debugged and checked everything.

foreach (var catno in CategoryNoList)
                { 
                    var ProdList = (from ProdRec in dbCon.Products
                                    where ProdRec.CatName == "catname" && ProdRec.CatNo == catno
                                    select ProdRec).First();
                    prod.Add(ProdList);
                }

In above code, catno is picking the right no but finally always the first sequence is added to the list in prod.Add(ProdList); If catno is 1, the first row is added, if catno is 2 the first row is added and so on.

One thing I can assure you, the data is coming from a database view not a table and the view doesn't have any Unique/Primary Key.

Thursday, July 19, 2018 12:23 PM

Please try below code for we are sure about the data

foreach (var catno in CategoryNoList)
            {
                var ProdList = (from ProdRec in dbCon.Products 
                   where ProdRec.CatName == "catname" && ProdRec.CatNo == 2 
              select ProdRec);

                foreach (var item in ProdList)
                {
                    Response.Write(item.CatName + " " + item.CatNo + "<br />");
                }
                
            }

Please try above code with a hard code value for example 2 instead of canto

Then put here your result

Thursday, July 19, 2018 12:54 PM

I already tried with hard coding the catno and it returns the correct row.

Thursday, July 19, 2018 1:05 PM

Based on your result we can get your data is correct and your problem is with 

==catno this return the wrong value

When you set hard code, you got correct valuvalue, if you set canto by for each you got the wrong the 

The below line return bad values

var CategoryNoList = DefaultConnection.Products.Where(p => p.CatName == "catname").Select(p => p.CatNo).ToList();

Thursday, July 19, 2018 1:10 PM

But the CategoryNoList get all correct valued. In foreach it loop through the correct values. Even in the where clause the catno is correct everytime but the returned row is always the first one.

Thursday, July 19, 2018 1:20 PM

Could you please put your response result here after running below code(copy & paste here, exactly this response)

foreach (var catno in CategoryNoList)
            {
                var ProdList = (from ProdRec in dbCon.Products 
                   where ProdRec.CatName == "catname" && ProdRec.CatNo == 2 
              select ProdRec);

                foreach (var item in ProdList)
                {
                    Response.Write(item.CatName + " " + item.CatNo + "<br />");
                }
                
            }

Thursday, July 19, 2018 1:23 PM

Name 1 2

Thursday, July 19, 2018 1:30 PM

Now put the below result. Please put the same exactly

foreach (var catno in CategoryNoList)
            {
                var ProdList = (from ProdRec in dbCon.Products 
                   where ProdRec.CatName == "catname" && ProdRec.CatNo == 2 
              select ProdRec);

                foreach (var item in ProdList)
                {
                    Response.Write(item.CatName + " " + item.CatNo + "<br />");
                }
                prod.Add(ProdList.First());
foreach (var item in prod)
                {
                    Response.Write(item.CatName + " " + item.CatNo + "<br />");
                }

            }

Thursday, July 19, 2018 2:10 PM

Name 1 2
Name 1 2
Name 1 2
Name 1 2
Name 1 2
Name 1 2
Name 1 2
Name 1 2
Name 1 2

Thursday, July 19, 2018 2:32 PM

That's right. Now put the below response

foreach (var catno in CategoryNoList)
            {
                var ProdList = (from ProdRec in dbCon.Products 
                   where ProdRec.CatName == "catname" && ProdRec.CatNo == catno 
              select ProdRec);

Response.Write("catno :"catno + "<br />");

                foreach (var item in ProdList)
                {
                    Response.Write(item.CatName + " " + item.CatNo + "<br />");
                }
                prod.Add(ProdList.First());
Response.Write("" + "<br />");

foreach (var item in prod)
                {
                    Response.Write(item.CatName + " " + item.CatNo + "<br />");
                }

            }

I'll wait for you

Thursday, July 19, 2018 5:07 PM

Sorry for being late. Here it is-

catno:1
Name 1 1

Name 1 1
catno:2
Name 1 1

Name 1 1
Name 1 1
catno:3
Name 1 1

Name 1 1
Name 1 1
Name 1 1

  • Last updated on