C# web API linq to SQL List object

Question

I'm trying create a web API for an autocomplete textbox. I have the API working with sample data, but I don't know how to add data from my table.

Here is my controller:

namespace IMDSEbs.Controllers
{
    public class CompanyController : ApiController
    {
        // GET: api/Company/GetCompanyNames
        IMDSDataContext dc = new IMDSDataContext();
        public List<CompanyName> results = new List<CompanyName>
        {
            new CompanyName{ID = 1, Name = "Sonu Nigam"},

        };

        // GET api/values
        public IEnumerable<CompanyName> GetCompanyNames(string query)
        {
            return results.Where(m => m.Name.Contains(query)).ToList();
        }
    }
}

Here is the table structure (Name is actually the company name):

ID              Name
------------------------------------
1               Abc Company
2               cde Company
3               fgh Company
4               ijk Company

Your question, is unclear. If you have your IEnumerable of Company names, call you controller using ajax and appended it to your client using javascript — johnny 5, May 17 '16 at 17:45
It works as is. I don't know how to use a table from sql server instead of the sample data. — ChrisPasa, May 17 '16 at 17:47

score 3 · Answer 1 · answered May 17 '16 at 17:49

If you trying to call your database? I'm assuming your context is EntityFramework based, if so you can do this:

dc.Companies.Where(x => x.Name.Contains(query)).Select(x => new {x.id, x.Name}).ToList();

Make sure you call your where when your grabbing you data otherwise you will grab all of the companies and the be filtering them in memory which is an extra step.

score 0 · Accepted Answer · answered May 17 '16 at 17:49

0

This is the answer i was looking for.

namespace IMDSEbs.Controllers
{
    public class CompanyController : ApiController
    {

        // GET api/values
        public IEnumerable<CompanyName> GetCompanyNames(string query)
        {
            IMDSDataContext dc = new IMDSDataContext();
            List<CompanyName> results = new List<CompanyName>();
            foreach(IMDSEbs.Models.Company comp in dc.Companies)
            {
                results.Add(new CompanyName()
                {
                    ID = comp.CompanyID,
                    Name = comp.CompanyName
                });
            }

            return results.Where(m => m.Name.Contains(query)).ToList();
        }


    }
}

answered May 17 '16 at 17:49

ChrisPasa

113
5
15

Just so you are aware, since you are calling a foreach on the dc.Companies, you will be hitting the database each time in the foreach, meaning if you have 1000, companies your calling into the DB 1000 times, so you will have the extra latency of the communication. If you're using EntityFramework, the where will be filtered on the DB so you only will call the DB once. If your using POCO will have to write your SQL Raw some where but still you will only be calling the DB once – johnny 5 May 17 '16 at 20:50
I'm using linq not entity framework. the query string is a parameter that only get one record from the database. If you have a better way please show me. LInq not entity framework. – ChrisPasa May 18 '16 at 12:40
Your data context should have a function that will return scalar results, if it doesn't you need to either create one or call. Create a Query `dc.Query("SELECT * FROM Companies WHERE name like '%" + query + " %' ").ToList();` – johnny 5 May 18 '16 at 13:54

score 0 · Answer 3 · answered Aug 29 '17 at 19:57

To get rid of the foreach do the following (may need a bit of touch up...)

return 
//Get the Entities you need...
dc.Companies
//Narrow down what you are selecting...
.Where(m => m.Name.Contains(query))
//convert the entity into the object you need...
.Select(comp => new CompanyName(){
                ID = comp.CompanyID,
               Name = comp.CompanyName})
.ToList();

C# web API linq to SQL List object

3 Answers3