CODEWITHSUNDEEP
mysqlentity-framework-4full-text-search
Dan C
Dan C

Reputation: 2231

How do I use full text search with MySql and Entity Framework 4.2 Code First?

I am using Entity Framework 4.2 Code First and MySQL. I need to be able to do a full text search across several fields of my table. However, I am having a number of issues with this:

1) Full text search under MySQL requires the MyISAM table type. However, when I run the DropCreateDatabaseAlways initializer, the table type I get is InnoDB. Here is an example of my table definition:

[Table("Patient")]
[Serializable]
public class Patient
{
    public Patient()
    {
    }

    [Key]
    public int PatientID { get; set; }

    [MaxLength(50)]
    public string FirstName { get; set; }
    [MaxLength(50)]
    public string LastName { get; set; }
}

Is it possible to specify MyISAM table type? If so, how?

2) I need to specify the full text index: 

ALTER TABLE `patient` ADD FULLTEXT INDEX `Name`(`FirstName`, `LastName`);

One possibility I can think of is:

public class MyDbInitializer : DropCreateDatabaseAlways<MyDbContext>
{
    protected override void Seed(MyDbContext context)
    {
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE `patient` ADD FULLTEXT INDEX `Name`(`FirstName`, `LastName`)");
    }
}

Is there a better way?

3) I need to be able to search using the full text index. Ideally in a way that works with my existing linq to SQL.

One less-then-desirable possiblity I can think of is to create a stored procedure (again, using ExecuteSqlCommand) that takes a string and returns either a list of matching PatientIDs or a list of Patient rows. For example: 

IEnumerable<int> patientIDs = [...call stored proc to get matching ids...]
var patients = from p in patients 
               where patientIDs.Any(pid => pid == p.PatientID) 
               select new {...}

Or:

var patients = from p in [...call stored proc to get matching patient records...]
               select new {...}

How would I do this? Is there a better way?

4) Bonus question: Even though I specify the [MaxLength(50)] attribute, my strings are being stored as MEDIUMTEXT instead of VARCHAR(50) as I would expect. Without the [MaxLength] attribute I get LONGTEXT. How do I specify VARCHAR(n) for strings types?

Any ideas?

Thanks in advance,

Dan

Upvotes: 2

Views: 1193

Answers (2)

Devart
Devart

Reputation: 122032

If you are a dotConnect for MySQL user, we advise you to take a look at this article. If you use some other provider, we advise you to contact its vendor.

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364409

  1. It is provider specific so unless your provider for MySQL offers some way to change it you will not be able to do that from code first. I think you should be able to run ALTER TABLE in custom DB initializer to change the engine.
  2. If you use migration feature (EF 4.3 and newer) you can script the index in the migration otherwise you need to use your initializer.
  3. You need to query the database with SQL. EF has no support for fulltext search queries. To call a stored procedure you must use SqlQuery and populate a type with same properties as the result set.
  4. Try to use [Column(TypeName="VARCHAR(50)] annotation.

Upvotes: 1

Related Questions