Question DB query ToList() returns correct number of rows, but return(list) shows multiples of the first query result.

[jkmclean]

I am relatively new to C# programming and have been tasked with completing a project after the original C# developer left the company. I apologize if this is easy...
I am writing a web API that needs to return SerialNumber(s) for gas containers/equipment that were shipped for a given order.

In trying to figure this out, I have broken the code in multiple lines to make it as step-by-step as possible, and am now using a simple SQL statement.

//Tried this first...
//List<tblShipped_Equipment> shippedEquip = await db.tblShipped_Equipment.Where(s => s.LotID == lotID).ToListAsync();

//Now using this...
string sqlstmt = "SELECT * FROM tblShipped_Equipment se WHERE se.LotID = @p0";
List<tbl_Shipped_Equipment> shippedEquip = new List<tblShipped_Equipment>();
shippedEquip = await db.tblShipped_Equipment.SqlQuery(sqlstmt, lotID).ToListAsync();

if (shippedEquip == null || shippedEquip.Count == 0)
{
    return NotFound();
}

return Ok(shippedEquip);

The process runs but it returns only the first value (note SerialNumber is SK030094) on all records. For this example (LotID) there are actually 12 rows in the DB, but I've only shown 4 results below.

DB Table:
LotID SerialNumber name Eqp_Type
B000000J0Q SK030094 NULL NULL
B000000J0Q SK030248 NULL NULL
B000000J0Q SK030140 NULL NULL
B000000J0Q SK030290 NULL NULL
B000000J0Q SK030357 NULL NULL
B000000J0Q SK030116 NULL NULL
B000000J0Q SK030302 NULL NULL
B000000J0Q SK030078 NULL NULL
B000000J0Q SK030196 NULL NULL
B000000J0Q SK030235 NULL NULL
B000000J0Q SK030378 NULL NULL
B000000J0Q SK030048 NULL NULL

API Results:
Version:1.0 StartHTML:000000224 EndHTML:000026290 StartFragment:000000407 EndFragment:000026234 StartSelection:000000407 EndSelection:000026234 SourceURL:http://localhost:55110/api/Shipped_Equipment?lotID=B000000J0Q
<tblShipped_Equipment><Eqp_Type i:nil="true"/><LotID>B000000J0Q</LotID><SerialNumber>SK030094</SerialNumber><name i:nil="true"/></tblShipped_Equipment>
<tblShipped_Equipment><Eqp_Type i:nil="true"/><LotID>B000000J0Q</LotID><SerialNumber>SK030094</SerialNumber><name i:nil="true"/></tblShipped_Equipment>
<tblShipped_Equipment><Eqp_Type i:nil="true"/><LotID>B000000J0Q</LotID><SerialNumber>SK030094</SerialNumber><name i:nil="true"/></tblShipped_Equipment>
<tblShipped_Equipment><Eqp_Type i:nil="true"/><LotID>B000000J0Q</LotID><SerialNumber>SK030094</SerialNumber><name i:nil="true"/></tblShipped_Equipment>
...

If I return Ok(shippedEquip.Count); I get "12".

Any idea why I'm only getting the 1st result repeated multiple times instead of the 12 different rows as expected?

Thanks in advance!
Jamie

 

[Skydiver]

if you set a breakpoint right after this line:

shippedEquip = await db.tblShipped_Equipment.SqlQuery(sqlstmt, lotID).ToListAsync();

and then inspect the shippedEquip list, are the first 4 items duplicated?
If yes, then you may need to try turning on more extensive SQL tracing to see what the exact SQL query looks like.
If no, then there is something about the Ok(shippedEquip) that is creating the duplicates.

 

[jkmclean]

Thanks for your prompt reply. I appreciate it.
I found the issue appears to be with the table tblShipped_Equipment. It has 4 columns, no primary key and only the LotID being set as NOT NULL. I created a new table with a unique ID column, copied all rows from tblShipped_Equipment into it, imported the model using EF, generated a Controller and it works as expected returning each individual SerialNumber related to the LotID.
With that, I'm going to assume the DB tables need to have a primary key, or some other unique identifier to work the way I was trying to use them.

 

[Skydiver]

I think that if you used a raw SQL query with SqlCommand against the table instead of using EF, things would have worked. It's EF that likes having unique row ids so that it knows where updates will go when an object is updated. Anyway glad you solved your problem.

 

[jkmclean]

Yes, I see what you're saying and that would probably work as I'm not the first person to be dealing with tables like that, so there has to be a way to do it.
Maybe when I get more proficient and have some time I'll try it with SqlCommand as suggested.
Thanks again.