What do i use with LINQ in order to READ database instead of SqlDataReader?

Question

I'm trying to use LINQ for the first time and don't understand how to read through the database. I'm sure I am doing something completely stupid and just need a little guidance.

year = year.Where(x => x.statementYear == response[i]).ToList();

SqlDataReader reader;

while (reader.Read())
{

}

Edited question with more info:

This is what I have been trying to get to work.. I don't think I need the con.open() either if I am using the using statement right?

using (SqlConnection con = new SqlConnection(".."))
{
    List<string> paths = new List<string>();

    // Open Connection
    con.Open();

    if (response != null)
    {
        for (var i = 0; i < response.Length; i++)
        {
            if (response != null)
            {
                using (var db = new db())
                {
                    List<ClientStatement_Inventory> years = new List<ClientStatement_Inventory>();

                    years = years.Where(x => x.statementYear == response[i]).ToList();

                    foreach (var year in years)
                    {
                        paths.Add(year.statementPath);
                    }
                }
            }
        }
    }
}

marc_s · Accepted Answer · 2020-06-03 20:48:21Z

1

When using Linq, there's no use or point in using SqlDataReader....

You really didn't show much to go on - but basically, with Linq, you should have a DbContext (that's your "connection" to the database), and that DbContext should contain any number of DbSet - basically representing the tables in your database. You select from these DbSet's and then you get back a List<Entity> which you just iterate over.

Something along the lines of:

-- select your customers matching a certain criteria
var customers = NorthwindDbContext.Customers.Where(x => x.statementYear == response[i]).ToList();

-- iterate over your customers
foreach (Customer c in customers)
{
     // do whatever with your "Customer" here
}

UPDATE:

from your updated question - all you really need is:

List<string> paths = new List<string>();

using (var db = new FMBDBPRDEntities1())
{
    List<ClientStatement_Inventory> years = new List<ClientStatement_Inventory>();

    years = years.Where(x => x.statementYear == response[i]).ToList();

    foreach (var year in years)
    {
        paths.Add(year.statementPath);
    }
}

That opens the context to access the database, reads some data into a list, iterates over the elements in the list - done. Everything else is useless and can be just deleted.

And you could write this a lot simpler, too:

using (var db = new FMBDBPRDEntities1())
{
    List<string> paths = years.Where(x => x.statementYear == response[i])
                              .Select(y => y.statementPath).ToList();
}

UPDATE #2: if your response is a collection of values (I'm assuming those would be int values), and you need to iterate over it - try something like this:

if (response != null)
{
    List<string> allPaths = new List<string>();

    using (var db = new FMBDBPRDEntities1())
    {
        foreach (int aYear in response)
        {
            List<string> paths = years.Where(x => x.statementYear == aYear)
                                      .Select(y => y.statementPath).ToList();

            allPaths.AddRange(paths);
        }
    }

    return allPaths; // or do whatever with your total results
}

UPDATE #3: just wondering - it seems you never really access the DbContext at all ..... I'm just guessing here - but something along the lines of:

if (response != null)
{
    List<string> allPaths = new List<string>();

    using (var db = new FMBDBPRDEntities1())
    {
        foreach (int aYear in response)
        {
            // access a DbSet from your DbContext here! 
            // To actually fetch data from the database.....
            List<string> paths = db.ClientStatement_Inventory
                                   .Where(x => x.statementYear == aYear)
                                   .Select(y => y.statementPath).ToList();

            allPaths.AddRange(paths);
        }
    }

    return allPaths; // or do whatever with your total results
}

edited Jun 3, 2020 at 20:48

answered Jun 3, 2020 at 19:16

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user10251956 Over a year ago

I updated my question with a little more code. With what I am trying it never fully runs through the foreach statement. It gives me this error: " data not available. see the locals window for available intellitrace data "

marc_s Over a year ago

@New_Coder: updated my response with showing you what you really need....

user10251956 Over a year ago

Thank you for your update.. But I still need a for loop to iterate through the response so that I can get the index of response right?

marc_s Over a year ago

@New_Coder: it's quite unclear from your code posted what that response really is - but if it's a collection, and you need to iterate over it - then yes, you need a loop - but no SqlDataReader or any of that, really ...

user10251956 Over a year ago

You are amazing!!!! Thank you so much for the help now I just gotta figure out how to download those files in my controller. Thank you for all the help!

|

Collectives™ on Stack Overflow

What do i use with LINQ in order to READ database instead of SqlDataReader?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related