Resolved Cannot implicitly convert type error

[Anonymous]

I am trying to fetch certain database values and store them in a datatable. After that I am applying linq to group by all the data with account number. I am storing the final result in a list which is of the following class type -

  class ListItems
    {
        public string accountNumbers
        {
            get;
            set;
        }
         
        public string itemNumbers
        {
            get;
            set;
        }

    }

My code is :

 List<ListItems> accounts = new List<ListItems>();

        public void GetItems(int siteID1, int siteID2)
        {
            con = new SqlConnection(connection);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_FetchItems";
            cmd.Parameters.AddWithValue("@siteid1", siteID1);
            cmd.Parameters.AddWithValue("@siteid2", siteID2);
            cmd.Connection = con;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adp.Fill(dt);

            accounts = from result in dt.AsEnumerable() group result by result.Field<string>("accountNumber");
           
        }

I am getting the following error on line no 16

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<System.Linq.IGrouping<string, System.Data.DataRow>>' to 'System.Collections.Generic.List<PricingPractice.ListItems>

I even tried to convert this expression using ToList() but it is also not working.

 

[Skydiver]

Your first step would be to populate an IEnumerable of ListItems with the data you get back from your database query instead of stuffing the data into a DataTable. That way you'll only be dealing with one type -- ListItems -- instead of now trying to go from DataRow to ListItems.

 

[Anonymous]

Your first step would be to populate an IEnumerable of ListItems with the data you get back from your database query instead of stuffing the data into a DataTable. That way you'll only be dealing with one type -- ListItems -- instead of now trying to go from DataRow to ListItems.

you mean I should do the following ?

IEnumerable<ListItems> listItems;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(listitems);

but I am also getting cast error here.

 

[Skydiver]

You don't need a SqlAdapter. All that does is fill a DataSet. You need to call ExecuteQuery() on your SqlCommand and iterate over the reader and create instances of your List items and stick them in a List<ListItems>.

 

[Anonymous]

You don't need a SqlAdapter. All that does is fill a DataSet. You need to call ExecuteQuery() on your SqlCommand and iterate over the reader and create instances of your List items and stick them in a List<ListItems>.

Okay, so I did the following. Called a data reader and created instances of my list item class and added them in my account list but still I need to group by my items with account number. Secondly, with this it is overwriting account number .

  SqlDataReader sqlDat = cmd.ExecuteReader();

            while(sqlDat.Read())
            {
                listItems.accountNumbers = sqlDat["accountNumber"].ToString();
                listItems.itemNumbers = sqlDat["itemnumber"].ToString();
                accounts.Add(listItems);
            }
            sqlDat.Close();

 

[JohnH]

created instances of my list item class

Where? Nowhere in your code are you using the new operator to create new item instances. new operator - C# reference

 

[Anonymous]

Where? Nowhere in your code are you using the new operator to create new item instances. new operator - C# reference

Oh sorry, I created a single instance at the top of my code so due to that it was overwriting my account number but the underlying issue still remains, I want my items to be grouped by account number and then I want to store the final result in a list . For group by I am using linq but it gives me cast error

  SqlDataReader sqlDat = cmd.ExecuteReader();

            while(sqlDat.Read())
            {
                ListItems listItems = new ListItems();
                listItems.accountNumbers = sqlDat["accountNumber"].ToString();
                listItems.itemNumbers = sqlDat["itemnumber"].ToString();
                accounts.Add(listItems);
            }
            sqlDat.Close();

            accountsandItemList = from result in accounts group result by result.accountNumbers;

 

[NoUserHere]

while(sqlDat.Read()) { ListItems listItems = new ListItems();

Tell me, how many times does that new list get created in that loop?

 

[JohnH]

Tell me, how many times does that new list get created in that loop?

It seems to me that ListItems is a single item (definition post 1), it is added to the accounts list. I don't quite understand why ListItems and it's members accountNumbers and itemNumbers is plural (instead of ListItem and members accountNumber and itemNumber).

 

[NoUserHere]

It seems to me that ListItems is a single item (definition post 1)

Thanks for making me look twice. I didn't notice. Late night working last night resulted in a 8AM crash out, and I only woke at 1:30PM. I still feel tired.

Yes I agree, the naming policy here is terrible. The object names need cleaning up for sure, and they need to be named in accordance of their purpose of use.

Why name something listItems, when it is nothing to do with a list of items at all? That class is only holding contextual property information, not a list. Name that class something decent that reflects its usage, like AccountDetails.
Then when declaring your list, you can have List<AccountDetails> in your declaration. And add your account details to that list when retrieving your data.

The quote below confirms more than one result is returned from the query they're using. So I assume they want more than one returned result?
Otherwise I guess they would have no need to use group.

Secondly, with this it is overwriting account number .

Here's the problems I see..

• Given more than one result is returned according to the OP's query, If they want a singular result, or a specific set of result(s), then the OP is using the wrong query to begin with. (if they want a single result or specific set of result(s)), often you should know what to select within a SQL statement. If you don't; you select * and narrow with a where clause and other additional clauses and then filter the results in linq to match a given condition if further narrowing is required. If any grouping is required, it should be done in the query and not after the query returns the result.
  
  
• Filtering in Linq should be a last resort, as you should only take from the database what you need, and you really should be doing as much filtering in the query returning your data instead of after the data is returned from your query.
  
  
• In code, group is used to group data together in a specific manner. If the OP wanted to return a single item, they they wouldn't be using group in Linq. This means they are returning all results when they don't need to.
  
  
• If you call to List() at the end of your code, you can return a list of the data you are grouping by your given condition.