CODEWITHSUNDEEP
asp.net-mvc-3razorentity-framework-4.1repository-pattern
Aman
Aman

Reputation: 1382

Repository pattern mvc3

I have a very basic question about repository pattern in c# mvc3.

I am using EF4.1 and database is with millions of records.

I need to fetch some data from SQL database based on some clause. The fetched data could be tens on thousands so certainly I have to use paging and sorting.

Problem here is the clause I need to have has to check element from XML stored as string in table as well as some other fields.

Like: this is my data model:

    public string ReportXML { get; set; }
    public DateTime CreatedOn { get; set; }

Report XML contain field 'VehicleNumber' which is my search criteria along with CreatedOn field.

what should be the best way to play with this XML as string so that only matches which satisfies the clause are fetched from DB..

Upvotes: 0

Views: 354

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

That is not a case for entity framework. What is even worse your database table has not sufficient structure for such kind of queries.

To query XML data in SQL server you need to use XML SQL data type - not string. Once you use XML data type you can use XPath or XQuery to query its content (you can also index content to improve performance of such queries). To write such queries you need to use native SQL. EF doesn't support XML column.

If you want to use it from EF you can try to replace your mapped table with view which will query the table with XML column but you will not be able to insert or update data without also using stored procedures or custom SQL commands mapped in EDMX file.

Upvotes: 4

Related Questions