SQL Query only returns one item (rather than complete list) - asp.net mvc

Question

4.00/5 (1 vote)

See more:

I have an SQL query within ASP.NET, but it only seems to return 1 result rather than filling the list with 4 as it should (as the query itself is correct)

Code:

C#

List<string> dropItems, dropItems2;

using (var dbContext = new DatabaseContext())
{
    dropItems = dbContext.Database.SqlQuery<String>(
        String.Format("SELECT locations.Code FROM locations")).ToList();
}
using (var dbContext = new DatabaseContext())
{
    dropItems2 = dbContext.Database.SqlQuery<String>(
        String.Format("SELECT locations.Name FROM locations")).ToList();
}

After the code executes, each list only contains one item rather than 4 which they both should

F.Y.I - The item I'm always receiving rather than the full 4 is just the last one

Posted 20-Oct-14 1:07am

ForumCrazy

Updated 20-Oct-14 1:11am

v5

Add a Solution

Comments

Nathan Minier 20-Oct-14 7:20am

Quick question: If you're using EF (which DatabaseContext infers), why aren't you using EF for data access(ie dbContext.Set<locations>().Select(x => new { x.Code, x.Name }).toList() ?

ForumCrazy 20-Oct-14 7:22am

Because I'm not super Knowledgeable in Linq. Will the code you have suggested solve the issue I am having?

Nathan Minier 20-Oct-14 7:47am

I'd suggest looking into using LINQ, it's very easy and intuitive once you get a grasp on it, and it will (often) do a better job shaping queries than you will with SQLCommand-type objects.

There is a very robust primer at http://www.codeproject.com/Articles/33769/Basics-of-LINQ-Lamda-Expressions

Kornfeld Eliyahu Peter 20-Oct-14 7:25am

Even I do not think that this is your problem - why the String.Format part?
Did you double checked the locations table (view?) in your database?

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)

Raul Iloc · Answer 1 · 2014-10-20T01:34:00

Solution 1

You should change your code like this:

C#

List<string> dropItems, dropItems2;

using (var dbContext = new DatabaseContext())
{
    dropItems = dbContext.Locations.Select( l => l.Code).ToList();
    dropItems2 = dbContext.Locations.Select( l => l.Name).ToList();
}

Posted 20-Oct-14 1:34am

Raul Iloc

Comments

Nathan Minier 20-Oct-14 7:44am

This will poll the database twice. You'd be better served with an anonymous object or a ViewModel, just as standard practice.

Raul Iloc 20-Oct-14 8:05am

Yes you are right, the code could be optimized, but I did not wanted to change the internal logic used by the originator of the question.

/\jmot · Answer 2 · 2014-10-21T21:26:00

Your Code Working Fine With me..
I can't find out why, you are not getting the result..

Try different code..I think this will solve your problem.

C#

List<string> dropItems, dropItems2;
 
using (var dbContext = new DatabaseContext())
{
var data = from o in dbContext.locations select new { o.Code };
foreach (var str in data)
{
 if(str!=null)
 {
  dropItems .Add(str.ToString());
 }
}
//dropItems = dbContext.Database.SqlQuery<string>(
 //String.Format("SELECT locations.Code FROM locations")).ToList();
}
using (var dbContext = new DatabaseContext())
{
var data1 = from o in dbContext.locations select new { o.Name };
foreach (var str1 in data1)
{
 if(str1!=null)
 {
  dropItems2 .Add(str1.ToString());
 }
}

    //dropItems2 = dbContext.Database.SqlQuery<string>(
        //String.Format("SELECT locations.Name FROM locations")).ToList();
}

</string></string></string>