Asp.net MVC 4 Database Query returning sql query.

Question

I am trying to query my database to get a specific data from my database. however when I convert the query to string it doesn't return the select value, instead it returns the whole SQL Query in a string. I am stumped on why this is happening

public ActionResult StudiedModules()
    {
        Model1 studiedModules = new Model1();
        List<StudiedModulesModel> listModules = new List<StudiedModulesModel>();
            using (EntityOne context = new EnitityOne())
            {
                foreach(var module in context.StudiedModules){
                    studiedModules.School = context.ModuleDatas.Where(p=>p.ModuleCode == module.ModuleCode).Select(u=>u.School).ToString();
                    studiedModules.Subject = context.ModuleDatas.Where(p=>p.ModuleCode == module.ModuleCode).Select(u=>u.Subject).ToString();
                }
            }

            var data = listModules;

        return View(data);
    }

Tim S · Accepted Answer · 2013-11-04 18:16:20Z

1

Calling ToString() on an Entity Framework Linq query like that will in fact return the SQL Query. So as it's written, your code is doing exactly what you wrote it to do.

If you want to select the first result from the IQueryable<T>, then you need to call First() before your ToString(). So, try changing

studiedModules.School = context.ModuleDatas.Where(p=>p.ModuleCode == module.ModuleCode).Select(u=>u.School).ToString();

studiedModules.Subject = context.ModuleDatas.Where(p=>p.ModuleCode == module.ModuleCode).Select(u=>u.Subject).ToString()

to

studiedModules.School = context.ModuleDatas.Where(p=>p.ModuleCode == module.ModuleCode).Select(u=>u.School).First().ToString();

studiedModules.Subject = context.ModuleDatas.Where(p=>p.ModuleCode == module.ModuleCode).Select(u=>u.Subject).First().ToString()

There are a whole lot more methods available depending on what you're trying to accomplish. If you want to get a list, use ToList(), or as Uroš Goljat pointed out, you can get a comma-separated list of values using the Aggregate( (a, b)=> a + ", " + b) method.

edited Nov 4, 2013 at 18:16

answered Nov 4, 2013 at 18:08

Tim S

2,32917 silver badges23 bronze badges

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

5 Comments

user1938460 Over a year ago

Thanks for your help, I have used First() - Which gives an error "Sequence contains no elements". So I used FirstOrDefault() which does not seems to get the correct values that I need, it seems to always get the first item within the database for all rows.

Tim S Over a year ago

The "Sequence contains no elements" is the exception that First() throws when there are no results to your query. The difference between First() and FirstOrDefault() is that if there are no results from the query, First() will throw that exception, and FirstOrDefault() will return null.

user1938460 Over a year ago

This may sound stupid, but I have found when I add to my list within the foreach loop it overwrites all previous data with current one while adding itself. Do you know why this may occur?

Tim S Over a year ago

You're not adding to your listModules instance of List<StudiedModulesModel>. You're just repeatedly changing your studiedModules instance of Model1.

user1938460 Over a year ago

Thanks Tim, you were right, I needed to create a new studiedModules for each loop. Thanks

Uroš Goljat · Accepted Answer · 2013-11-04 18:10:30Z

0

How about using Aggregate( (a, b)=> a + ", " + b) instead of ToString().

Regards, Uros

answered Nov 4, 2013 at 18:10

Uroš Goljat

1,81615 silver badges15 bronze badges

Collectives™ on Stack Overflow

Asp.net MVC 4 Database Query returning sql query.

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related