Saturday, November 2, 2024
HomeProductsADO.NET Data ProvidersUsing Oracle Full-Text Search in Entity Framework
ADO.NET Data ProvidersHow To

Using Oracle Full-Text Search in Entity Framework

dotConnect Team
By dotConnect Team
0
6547

Introduction

Oracle database supports an advanced functionality of full-text search (FTS) called Oracle Text, which is described comprehensively in the documentation:

We decided to meet the needs of our users willing to take advantage of the full-text search in Entity Framework and implemented the basic Oracle Text functionality in our Devart dotConnect for Oracle ADO.NET Entity Framework provider.
For working with Oracle Text specific functions in LINQ to Entities queries, the new OracleTextFunctions class is used, which is located in the Devart.Data.Oracle.Entity.dll assembly. It enables working with such Oracle Text functions as:

  • CONTAINS
  • CATSEARCH
  • MATCHES
  • SCORE
  • MATCH_SCORE

To call Oracle Text specific stored procedures of the CTX_DDL package, the OracleCtxDdlPackage class is used, which is located in the Devart.Data.Oracle.Entity.dll assembly.
To customize Code-First Migrations with the purpose of creating and deleting Oracle Text specific indexes, the CreateIndexConfiguration and DropIndexConfiguration classes are used, which are located in the Devart.Data.Oracle.Entity.Migrations.dll assembly.
This article deals with the following:


Tutorial Overview

In this tutorial you will learn how to create an application which will use the following technologies:

Follow the link at the end of this article to download the complete application sample.
In spite of the fact that in this sample we show work with Code-First, Oracle Text queries can be similarly written for reqular XML mapping using EDMX/EDML models developed within Database-First or Model-First approaches. So we will also briefly consider the Database-First approach in this tutorial.
We decided to take the standard Oracle tutorial as a basis, it is available at Getting Started with Oracle Text.

Prerequisites

Presetting Database and Model under the Database-First Approach

1. Create a console C# project in Visual Studio 2010 SP1 and call it OracleFullTextSearch.
2. In the project, add references to the following assemblies:

  • Devart.Data;
  • Devart.Data.Oracle;
  • Devart.Data.Oracle.Entity;
  • Devart.Data.Oracle.Entity.Migrations.

3. Also, an Oracle user is required, having the CTXAPP role and the EXECUTE grant for CTXSYS packages.
One of the ways to create such user is to execute the following Oracle statements via any tool designed for working with Oracle database:

CREATE USER myuser IDENTIFIED BY myuser_password;
GRANT RESOURCE, CONNECT, CTXAPP TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_THES TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO myuser;

4. Create an empty Entity Framework model for this user via Entity Developer. In the Solution Explorer window, call the context menu of the project, then select Add -> New Item -> Devart Entity Model. Follow the wizard instructions and specify a valid connection string. The file of the model and the generated code will be added to the project.

Presetting Database and Model under the Code-First Approach

1. Create a console C# project in Visual Studio 2010 SP1 and call it OracleFullTextSearch.
2. In the project, add references to the following assemblies:

  • System.Data.Entity;
  • Devart.Data;
  • Devart.Data.Oracle;
  • Devart.Data.Oracle.Entity;
  • Devart.Data.Oracle.Entity.Migrations.

3. Add the latest public version of EF Code-First to the project, for this run Package Manager Console via the Visual Studio menu: Tools -> Library Package Manager -> Package Manager Console, and in the displayed window run the following command:

Install-Package EntityFramework

As a result, the EntityFramework.dll assembly is added to the project, App.config is supplemented with records and the packages.config file is created.
4. Add a new MyContext.cs file to the project and place an empty model in it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using Devart.Data.Oracle;
using Devart.Data.Oracle.Entity;
using Devart.Data.Oracle.Entity.Configuration;

namespace OracleFullTextSearch {

  public class MyContext: DbContext {

    private OracleCtxDdlPackage ctxDdl;

    public MyContext() {
    }

    public MyContext(OracleConnection connection)
      : base(connection, false) {
    }

    static MyContext() {

      var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
      config.Workarounds.DisableQuoting = true;
      config.CodeFirstOptions.UseNonUnicodeStrings = true;
    }

    public OracleCtxDdlPackage CtxDdl {
      get {
        if (this.ctxDdl == null)
          this.ctxDdl = new OracleCtxDdlPackage((OracleConnection)Database.Connection);
        return this.ctxDdl;
      }
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {

    }

  }
}

Here, in order not to overload the sample with quoted identifiers, we have also set the DisableQuoting configuration option value of the EF provider to True, to get DDL and DML unquoted identifiers during SQL generation. It is not necessary to do this in user applications.
Besides, we have set the UseNonUnicodeStrings option value to True, to avoid specifying explicitly string data type [Column(TypeName=”varchar2″)] for entity string properties, so that specifying just the length limit [MaxLength(200)] would be enough.
We have also added the CtxDdl property of the OracleCtxDdlPackage type for working with stored procedures from the CTX_DDL package.
5. Run the following command in Package Manager Console:

Enable-Migrations

As a result, the Configuration class is added to the project.
6. Complement the existing Configuration class constructor

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;
    }

by specifying the SQL generator:

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;

      SetSqlGenerator(Devart.Data.Oracle.Entity.Migrations.OracleConnectionInfo.InvariantName,
              new Devart.Data.Oracle.Entity.Migrations.OracleEntityMigrationSqlGenerator());
    }

7. Also, an Oracle user is required, having the CTXAPP role and the EXECUTE grant for CTXSYS packages.
To create such user, you can e.g. execute the following Oracle statements via any tool designed for working with Oracle database:

CREATE USER myuser IDENTIFIED BY myuser_password;
GRANT RESOURCE, CONNECT, CTXAPP TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_THES TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO myuser;

8. Setting the connection string for our class of the MyContext model will be performed in the config file of the application. Add the connection string to the App.config file:

<connectionStrings>
  <add name="MyContext" connectionString="User Id=myuser;Password=myuser_password;Server=ORA;" providerName="Devart.Data.Oracle" />
</connectionStrings>

Using CONTEXT Index

Presetting Database and Model under the Database-First Approach

1. Create the DOCS table and the IDX_DOCS CONTEXT index.

CREATE TABLE docs (
  id NUMBER PRIMARY KEY,
  text VARCHAR2(200)
);

CREATE INDEX idx_docs ON docs(text)
     INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
     ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

2. Use the Update From Database Wizard to add the DOCS table to the EF model.

Presetting Database and Model under the Code-First Approach

1. Add the Doc.cs file to the project:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;

namespace OracleFullTextSearch {

  public class Doc {

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [MaxLength(200)]
    public string Text { get; set; }

  }
}

Here we specify that the Id property is a primary key, but its value will not be server-generated.
For the Text property we set the VARCHAR(200) data type.
2. Add the following line to the MyContext class:

    public DbSet<Doc> Docs { get; set; }

3. To generate migration, run the following command in Package Manager Console:

Add-Migration AddDoc

As a result, a migration class with the following content is added to the project:

    public partial class AddDoc : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Docs",
                c => new
                    {
                        Id = c.Int(nullable: false),
                        Text = c.String(maxLength: 200, unicode: false),
                    })
                .PrimaryKey(t => t.Id);

        }

        public override void Down()
        {
            DropTable("Docs");
        }
    }

4. Complement the migration with commands for creating (and deleting) the CTXSYS.CONTEXT index:

    using Devart.Data.Oracle.Entity.Migrations;

    public partial class AddDoc : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Docs",
                c => new
                    {
                        Id = c.Int(nullable: false),
                        Text = c.String(maxLength: 200, unicode: false),
                    })
                .PrimaryKey(t => t.Id);

            CreateIndex(
              table: "Docs",
              column: "Text",
              name: "idx_docs",
              anonymousArguments: new OracleCreateIndexConfiguration() {
                CtxIndexType = CtxIndexType.Context,
                CtxParameters = "FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP"
              });
        }

        public override void Down()
        {
            DropIndex(table: "Docs", name: "idx_docs");
            DropTable("Docs");
        }
    }

5. Update the database using the migration, by running the following command in Package Manager Console:

Update-Database -Verbose

As a result, commands for model objects creation will be executed

CREATE TABLE Docs ( 
  Id NUMBER(10) NOT NULL,
  Text VARCHAR2(200 CHAR) NULL,
  PRIMARY KEY (Id)
)

CREATE INDEX idx_docs ON Docs (Text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP')

Also, commands for creating system MigrationHistory table and filling it with migration data will be executed.

Full-Text Search Sample Using CONTAINS and SCORE Functions

In this tutorial, we have turned on OracleMonitor to view executed DDL and DML statements in the Devart dbMonitor application. However, keep in mind that the use of OracleMonitor is feasible for the purpose of testing and debugging but should be limited in production environments, since monitoring can decrease the performance of your application.
To use monitoring, add this code to your application

var monitor = new OracleMonitor() { IsActive = true };

and run dbMonitor.
Let’s write the code filling the table with data, updating the index and forming queries with the help of the CONTAINS and SCORE functions.

using (var ctx = new MyContext()) {

  // Load documents into the DOCS table
  ctx.Docs.Add(new Doc() { Id = 1, Text = "California is a state in the US." });
  ctx.Docs.Add(new Doc() { Id = 2, Text = "Paris is a city in France." });
  ctx.Docs.Add(new Doc() { Id = 3, Text = "France is in Europe." });
  ctx.SaveChanges();

  // Synchronize the index
  ctx.CtxDdl.SyncIndex("IDX_DOCS", "2M");

  // Querying your table with CONTAINS
  // Write a LINQ to Entities analogue for the SQL query:
  // SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0
  var firstQuery = ctx.Docs
    .Where(doc => OracleTextFunctions.Contains(doc.Text, "France", 1) > 0)
    .Select(doc => new {
      Score = OracleTextFunctions.Score(1),
      Doc = doc
    });
  var firstQueryResults = firstQuery.ToList();

  // Output:
  Console.WriteLine("1) CONTAINS('France')");
  foreach (var item in firstQueryResults)
    Console.WriteLine("{0,-5} | {1,-5} | {2}", item.Score, item.Doc.Id, item.Doc.Text);

  // Add some rows to the DOCS table
  ctx.Docs.Add(new Doc() { Id = 4, Text = "Los Angeles is a city in California." });
  ctx.Docs.Add(new Doc() { Id = 5, Text = "Mexico City is big." });
  ctx.SaveChanges();

  // Write a LINQ to Entities analogue for the SQL query:
  //   SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0
  var secondQuery = ctx.Docs
    .Where(doc => OracleTextFunctions.Contains(doc.Text, "city", 1) > 0)
    .Select(doc => new {
      Score = OracleTextFunctions.Score(1),
      Doc = doc
    });
  var secondQueryResults = secondQuery.ToList();

  // Output:
  Console.WriteLine();
  Console.WriteLine("2) CONTAINS('city')");
  foreach (var item in secondQuery)
    Console.WriteLine("{0,-5} | {1,-5} | {2}", item.Score, item.Doc.Id, item.Doc.Text);

  // Synchronize the index
  ctx.CtxDdl.SyncIndex("IDX_DOCS", "2M");

  // Execute the 2nd query once again
  var secondQueryNewResults = secondQuery.ToList();

  // Output:
  Console.WriteLine();
  Console.WriteLine("3) CONTAINS('city') after index synchronization");
  foreach (var item in secondQuery)
    Console.WriteLine("{0,-5} | {1,-5} | {2}", item.Score, item.Doc.Id, item.Doc.Text);
}

Using CTXCAT Index

Presetting Database and Model under the Database-First Approach

1. Create the AUCTION table, the AUCTION_ISET index set and the AUCTION_TITLEX CTXCAT index.

CREATE TABLE auction(
  item_id NUMBER,
  title VARCHAR2(100),
  category_id NUMBER,
  price NUMBER,
  bid_close DATE
);

EXEC CTX_DDL.CREATE_INDEX_SET('auction_iset');

EXEC CTX_DDL.ADD_INDEX('auction_iset','price');

CREATE INDEX auction_titlex ON AUCTION(title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set auction_iset');

2. Use the Update From Database Wizard to add the AUCTION table to the EF model.

Presetting Database and Model under the Code-First Approach

1. Add the Auction.cs file to the project:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace OracleFullTextSearch {

  [Table("auction")]
  public class Auction {

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("item_id")]
    public int Id { get; set; }

    [MaxLength(100)]
    public string Title { get; set; }

    [Column("category_id")]
    public int CategoryId { get; set; }

    public decimal Price { get; set; }

    [Column("bid_close")]
    public DateTime BidClose { get; set; }

  }
}

2. Add the following line to the MyContext class:

  public DbSet<Auction> Auctions { get; set; }

3. To generate migration, run the following command in Package Manager Console:

Add-Migration AddAuction

As a result, a migration class with the following content is added to the project:

    public partial class AddAuction : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "auction",
                c => new
                    {
                        item_id = c.Int(nullable: false),
                        Title = c.String(maxLength: 100, unicode: false),
                        category_id = c.Int(nullable: false),
                        Price = c.Decimal(nullable: false, precision: 18, scale: 2),
                        bid_close = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.item_id);

        }

        public override void Down()
        {
            DropTable("auction");
        }
    }

4. Complement the migration with commands for creating (and deleting) the CTXSYS.CTXCAT index and the corresponding index set:

    using Devart.Data.Oracle.Entity.Migrations;

    public partial class AddAuction : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "auction",
                c => new
                    {
                        item_id = c.Int(nullable: false),
                        Title = c.String(maxLength: 100, unicode: false),
                        category_id = c.Int(nullable: false),
                        Price = c.Decimal(nullable: false, precision: 18, scale: 2),
                        bid_close = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.item_id);

            CreateIndex("auction",
                    "Title",
                    name: "auction_titlex",
                    anonymousArguments: new OracleCreateIndexConfiguration() {
                      CtxIndexType = CtxIndexType.CtxCat,
                      CtxIndexSet = "auction_iset",
                      CtxIndexSetColumns = "price"
                    });
        }

        public override void Down()
        {
            DropIndex(table: "auction", name: "auction_titlex",
                  anonymousArguments: new OracleDropIndexConfiguration() {
                    Force = true,
                    CtxIndexSet = "auction_iset"
                  });

            DropTable("auction");
        }
    }

5. Update the database using the migration, by running the following command in Package Manager Console:

Update-Database -Verbose

As a result, commands for model objects creation

CREATE TABLE auction ( 
  item_id NUMBER(10) NOT NULL,
  Title VARCHAR2(100 CHAR) NULL,
  category_id NUMBER(10) NOT NULL,
  Price NUMBER(18,2) NOT NULL,
  bid_close TIMESTAMP(7) NOT NULL,
  PRIMARY KEY (item_id)
);

BEGIN
  CTX_DDL.CREATE_INDEX_SET('auction_iset');
  CTX_DDL.ADD_INDEX('auction_iset', 'price');
END;

CREATE INDEX auction_titlex ON auction (Title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('INDEX SET auction_iset');

and the command for filling the system HistoryTable table with the implemented migration data will be executed.

Full-text search sample using CATSEARCH function

Let’s write the code filling the AUCTION table with data and forming Oracle Text queries with the help of the CATSEARCH function. There is no need to update the index explicitly, as the CTXCAT index is updated automatically.

using (var ctx = new MyContext()) {

  // Pupulate the AUCTION table
  ctx.Auctions.Add(new Auction { Id = 1, Title = "NIKON CAMERA", CategoryId = 1, Price = 400, BidClose = new DateTime(2002, 10, 24) });
  ctx.Auctions.Add(new Auction { Id = 2, Title = "OLYMPUS CAMERA", CategoryId = 1, Price = 300, BidClose = new DateTime(2002, 10, 25) });
  ctx.Auctions.Add(new Auction { Id = 3, Title = "PENTAX CAMERA", CategoryId = 1, Price = 200, BidClose = new DateTime(2002, 10, 26) });
  ctx.Auctions.Add(new Auction { Id = 4, Title = "CANON CAMERA", CategoryId = 1, Price = 250, BidClose = new DateTime(2002, 10, 27) });
  ctx.SaveChanges();

  // Querying your table with CATSEARCH
  // Write a LINQ to Entities analogue for the SQL query:
  //   SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price') > 0;
  var firstQuery = ctx.Auctions
    .Where(a => OracleTextFunctions.Catsearch(a.Title, "CAMERA", "order by price") > 0)
    .Select(a => new { a.Title, a.Price });
  var firstQueryResults = firstQuery.ToList();

  // Output
  Console.WriteLine("1) order by price");
  foreach (var item in firstQueryResults)
    Console.WriteLine("{0,-20} | {1}", item.Title, item.Price);

  // Write a LINQ to Entities analogue for the SQL query:
  //   SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'price <= 300') > 0;
  var secondQuery = ctx.Auctions
    .Where(a => OracleTextFunctions.Catsearch(a.Title, "CAMERA", "price <= 300") > 0)
    .Select(a => new { a.Title, a.Price });
  var secondQueryResults = secondQuery.ToList();

  // Output
  Console.WriteLine();
  Console.WriteLine("2) price <= 300");
  foreach (var item in secondQueryResults)
    Console.WriteLine("{0,-20} | {1}", item.Title, item.Price);

  // Add some rows to the DOCS table
  ctx.Auctions.Add(new Auction { Id = 5, Title = "FUJI CAMERA", CategoryId = 1, Price = 350, BidClose = new DateTime(2002, 10, 28) });
  ctx.Auctions.Add(new Auction { Id = 6, Title = "SONY CAMERA", CategoryId = 1, Price = 310, BidClose = new DateTime(2002, 10, 28) });
  ctx.SaveChanges();

  // Execute the 1st query once again
  var firstQueryNewResults = firstQuery.ToList();

  // Output
  Console.WriteLine();
  Console.WriteLine("3) order by price (after adding new records)");
  foreach (var item in firstQueryNewResults)
    Console.WriteLine("{0,-20} | {1}", item.Title, item.Price);
}

Using CTXRULE Index

Presetting Database and Model under the Database-First Approach

1. Create the QUERIES table and the CTXRULE index for its column

CREATE TABLE queries (
  query_id      NUMBER,
  query_string  VARCHAR2(80)
);

CREATE INDEX queryx ON queries(query_string) INDEXTYPE IS CTXSYS.CTXRULE;

2. Use the Update From Database Wizard to add the QUERIES table to the EF model.

Presetting Database and Model under the Code-First Approach

1. Add the Query.cs file to the project:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace OracleFullTextSearch {

  public class Query {

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("query_id")]
    public int Id { get; set; }

    [Column("query_string")]
    [MaxLength(80)]
    public string QueryString { get; set; }

  }
}

2. Add the following line to the MyContext class:

public DbSet<Query> Queries { get; set; }

3. Generate migration by running the following command in Package Manager Console:

Add-Migration AddQuery

As a result, a migration class with the following content is added to the project:

    public partial class AddQuery : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Queries",
                c => new
                    {
                        query_id = c.Int(nullable: false),
                        query_string = c.String(maxLength: 80, unicode: false),
                    })
                .PrimaryKey(t => t.query_id);

        }

        public override void Down()
        {
            DropTable("Queries");
        }
    }

4. Complement the migration with commands for creating (and deleting) the CTXSYS.CTXRULE index:

    using Devart.Data.Oracle.Entity.Migrations;

    public partial class AddQuery : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Queries",
                c => new
                    {
                        query_id = c.Int(nullable: false),
                        query_string = c.String(maxLength: 80, unicode: false),
                    })
                .PrimaryKey(t => t.query_id);

            CreateIndex(
                table: "Queries",
                column: "query_string",
                name: "queryx",
                anonymousArguments: new OracleCreateIndexConfiguration() {
                  CtxIndexType = CtxIndexType.CtxRule
                });
        }

        public override void Down()
        {
            DropIndex(table: "Queries", name: "queryx");
            DropTable("Queries");
        }
    }

5. Update the database using the migration, by running the following command in Package Manager Console:

Update-Database -Verbose

As a result, commands for model objects creation

CREATE TABLE Queries ( 
  query_id NUMBER(10) NOT NULL,
  query_string VARCHAR2(80 CHAR) NULL,
  PRIMARY KEY (query_id)
);

CREATE INDEX queryx ON Queries (query_string) INDEXTYPE IS CTXSYS.CTXRULE;

and the command for filling the system HistoryTable table with the implemented migration data will be executed.

Full-Text Search Sample Using the MATCHES Function

Let’s write the code filling the table with data, updating the index and forming queries with the help of the MATCHES function.

using (var ctx = new MyContext()) {

  // Load documents into the QUERIES table
  ctx.Queries.Add(new Query() { Id = 1, QueryString = "oracle" });
  ctx.Queries.Add(new Query() { Id = 2, QueryString = "larry or ellison" });
  ctx.Queries.Add(new Query() { Id = 3, QueryString = "oracle and text" });
  ctx.Queries.Add(new Query() { Id = 4, QueryString = "market share" });
  ctx.SaveChanges();

  // Synchronize the index
  ctx.CtxDdl.SyncIndex("QUERYX", "2M");

  // Classify with MATCHES
  // Write a LINQ to Entities analogue for the SQL query:
  //   SELECT query_id,query_string FROM queries WHERE MATCHES(query_string,
  //     'Oracle announced that its market share in databases increased over the last year.') > 0;
  var firstQuery = ctx.Queries
    .Where(q => OracleTextFunctions.Matches(q.QueryString, "Oracle announced that its market share in databases increased over the last year.") > 0);
  var firstQueryResults = firstQuery.ToList();

  // Output:
  Console.WriteLine("Classify with MATCHES");
  Console.WriteLine();
  foreach (var item in firstQueryResults)
    Console.WriteLine("{0,-10} | {1}", item.Id, item.QueryString);
}

Conclusion

Thus, we have shown how to use Oracle Text functionality in Entity Framework applications, including the development process, based on Code-First Migrations. We are looking forward to receiving feedback from you via comments, the Entity Framework forum and our contact form, if it’s necessary to rework or improve support of Oracle Text in our provider.

You can download the archive with the complete version of the sample from this tutorial here.

dotConnect Team
dotConnect Team
RELATED ARTICLES

Whitepaper

Social

Topics

How To452What’s New316Events77Industry Insights36

Products

SQL Server Tools362MySQL Tools182ADO.NET Data Providers94PostgreSQL Tools90Oracle Tools87Delphi DAC77ODBC Drivers69Productivity Tools57ORM Solutions47SSIS Components24Excel Add-ins20

Devart

Founded in 1997, Devart is currently one of the leading developers of database management software, ALM solutions and data providers for most popular database servers. © 2024 Devart. All rights reserved.

Products
Support
Tutorials

Company