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
– johnny 5, Commented May 17, 2016 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
– ChrisPasa, Commented May 17, 2016 at 17:47

johnny 5 · Accepted Answer · 2016-05-17 17:49:18Z

3

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.

answered May 17, 2016 at 17:49

johnny 5

21.2k55 gold badges138 silver badges217 bronze badges

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

Comments

ChrisPasa · Accepted Answer · 2016-05-17 17:49:57Z

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, 2016 at 17:49

ChrisPasa

1135 silver badges16 bronze badges

3 Comments

johnny 5 Over a year ago

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

ChrisPasa Over a year ago

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.

johnny 5 Over a year ago

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<IMDSEbs.Models.Company>("SELECT * FROM Companies WHERE name like '%" + query + " %' ").ToList();

MecurioJ · Accepted Answer · 2017-08-29 19:57:32Z

0

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();

answered Aug 29, 2017 at 19:57

MecurioJ

763 bronze badges

Collectives™ on Stack Overflow

C# web API linq to SQL List object

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related