CODEWITHSUNDEEP
c#asp.netasp.net-mvcentity
Junior
Junior

Reputation: 11990

C# ASP.NET MVC 5 How to execute a raw query?

I am trying to execute a raw query using c#.

Here is what I have done 

var accounts = conn.Database.SqlQuery<IEnumerable<string>>("SELECT TOP 1 a.* FROM zipcodes_to_accounts AS c " +
"INNER JOIN accounts AS a ON a.id = c.account_id " +
"WHERE c.zip_code = @p0 "+
"ORDER BY a.completed_ll + a.completed_cp ASC", zipcode).ToArray();

Then I want to take the first record and convert it to json object.

if (accounts.Count() > 0)
{
    return JsonConvert.SerializeObject( accounts.First() ); 
}

But the query is giving me an error

The result type 'System.Collections.Generic.IEnumerable`1[System.String]' may not be abstract and must include a default constructor.

The accounts table has some columns that are varchar, datetime, integers. How can I get this query to work?

UPDATED Converting the IEnumerable to list like advised in the answer it working. but now the JsonConvert is returning an empty object. Here is my current code

       //getAccount
        public string GetAccount()
        {


            string zipcode = StringHelpers.StringOrNull(Request["zip_code"]);

            if (zipcode != null)
            {

                var accounts = conn.Database.SqlQuery<List<string>>("SELECT TOP 1 a.* FROM zipcodes_to_accounts AS c " +
"INNER JOIN accounts AS a ON a.id = c.account_id "+
"WHERE c.zip_code = @p0 "+
"ORDER BY a.completed_ll + a.completed_cp ASC", zipcode).ToList();
                var firstAccount = accounts.FirstOrDefault();

                if (firstAccount != null)
                {
                    return JsonConvert.SerializeObject(firstAccount);
                }

            }

            return "{}";

        }

When I debug my code Here is what I see

enter image description here

Upvotes: 1

Views: 2205

Answers (2)

Kevin Gosse
Kevin Gosse

Reputation: 39007

Not sure what ORM you're using, but the warning is telling you that IEnumerable cannot be constructed as it's an interface. Therefore the SqlQuery method can't know what return type you're expecting.

Try replacing the generic constraint with a concrete type:

var accounts = conn.Database.SqlQuery<string>("SELECT TOP 1 a.* FROM zipcodes_to_accounts AS c " +
"INNER JOIN accounts AS a ON a.id = c.account_id " +
"WHERE c.zip_code = @p0 "+
"ORDER BY a.completed_ll + a.completed_cp ASC", zipcode).ToArray();

Upvotes: 4

brandon-irl
brandon-irl

Reputation: 115

You're asking for a IEnumerable<string> which is an interface. You need to pick a class that implements IEnumerable such as a List

Upvotes: 2

Related Questions