search for int id starting with x entity framework 4.1

I currently have an Entity Framework model that collects data from a legacy database and I am currently using an int on my Id properties

I am attempting to build a search box with autocomplete capabilities and want to have the autocomplete function to return a subset of records based on whether the sample id either contains or starts with (final design decision not made yet) and I am running into problems with converting the integer id to a string as I would normally use a recs.Id.toString().StartsWith(recordId) but this is apparently not supported by the Entity Framework

Is there a way around this limitation ?

My code looks like the following

Model:

public class Sample
{
  public Sample()
  {
      Tests = new List<Test>();
  }

public          int                     Id              { get; set; }
public          DateTime                SampleDate      { get; set; }
public          string                  Container       { get; set; }
public          string                  Product         { get; set; }
public          string                  Name            { get; set; }
public          string                  Status          { get; set; }

public virtual SamplePoint SamplingPoint { get; set; }
public virtual SampleTemplate SampleTemplate { get; set; }
public Customer ForCustomer { get; set; }
public virtual ICollection<Test> Tests { get; set; }
}

and the query I am currently trying to apply to this model

[HttpGet]
public JsonResult AutoComplete(string partialId)
{
    var filteredSamples = 
                repo.AllSamples.Where( s =>                                   
                                String.Compare(s.Status, "A", false) == 0
                                && (s开发者_如何学C.Id.ToString()).StartsWith(partialId)
                        ).ToList();

    return Json(filteredSamples, JsonRequestBehavior.AllowGet);
}

Any ideas would be awesome I am out of ideas at this point

No matter what you do, this is going to result in some awful performance on large datasets, because you will not be able to use any indices. My recommendation would be to use a trigger or scheduled task to store the leading digit in a separate field and filter on that.

I ended up adding a view for autocomplete data and converting the data to string in the select statement and this solved my issue

Wild thought: how about your create a computed, persisted column on your database table, that converts your ID (INT) into a string?

Then you could:

• put an index on that column
• use a simple string comparison on that string column

Basically, you need this:

ALTER TABLE dbo.YourTable
  ADD IDAsText AS CAST(ID AS VARCHAR(10)) PERSISTED

Now update you EF model - and now you should have a new string field IDAsText in your object class. Try to run your autocomplete comparisons against that string field.