Q2 2010 Data Model and query in SQL

[Rafael]

Hi,

I after using the "old" API for 2 years now, i've jumped into this new "Data Model" Api and i must confess it's really nice to work with.
However, there are time that a SQL query would be a lot easier instead of trying to do it in Linq.
For example, when you need to fetch products according to several criteria that can be optional.

My question is: is there a simple way of using the new Data Model and being able to query it using SQL like in "OA Classic":

IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
           var query = @"select * from employees as x where x.employeeId = 1";
           var result = scope.GetSqlQuery(query, typeof(Employee), null).Execute();
           foreach (Employee ord in result)
           {
               Console.WriteLine(ord.EmployeeID);
           }

(i mean, besides using stored procedures..)

Regards,

Rafael

[Shawn Krivjansky]

I'm assuming you are using LINQ with the "new" API and you don't have the "dynamic" ability that writing straight SQL would give.

I'm guessing that the recommended solution would be to get "Dynamic Linq" working for your project:
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

That will give you the best of both worlds and is fairly easy to get going.

[IT-Als]

Hi Rafael,

You will need the instance of IObjectScope to do so.

You are using the new Context API as I understand,,and below each Context there's an IObjectScope instance, but it is not exposed in the Context.
However, you can gain access to it creating a partial class to extend your Context and expose a read-only property Scope, that calls the protected method GetScope() on the Context.
Then you can use your new Scope property on your Context to do what you need. In pseudo code something like this:

public partial class MyContext
{
   public IObjectScope Scope
   {
      get { return this.GetScope(); }
   }
}

Usage:

MyContext ctx = .....

ctx.Scope.GetSqlQuery(.....);

Regards

Henrik

[Rafael]

@ Henrik:

That sounds interresting.
However in the Best Practices for Telerik OpenAccess ORM Q1 2010 Release:
If the project is enhanced with previous version of Telerik OpenAccess ORM, do not add the new designer files to this project. Use the new OpenAccess Visual Designer for new projects.

Is it "bad" to mix both worlds?

Rafael

[Rafael]

@Shawn Krivjansky

Thanks for your reply.
I didn't knew this Dynamic Linq Library. I'm going to look into it.

Meanwhile what about this "work around" for these kind of situations:

myDB dbContext = GetmyDBContext();
IQueryable<WineScores> result = (from c in dbContext.WineScores
                                         //where countries.Contains(c.Country)
                                         //&& vintages.Contains(c.Vintage)
                                         //&& c.Price > minPricenew
                                         //&& c.Price < maxPricenew
                                         orderby c.Vintage descending
                                         select c);
if (countries.Count > 0)
    result = result.Where(c => countries.Contains(c.Country));
if (regions.Count > 0)
    result = result.Where(c => regions.Contains(c.Region));
if (vintages.Count > 0)
    result = result.Where(c => vintages.Contains(c.Vintage));
if (!String.IsNullOrEmpty(minPrice))
    result = result.Where(c => c.Price > minPrice);
if (!String.IsNullOrEmpty(maxPrice))
    result = result.Where(c => c.Price < maxPrice);
if (result.ToList().Count > 0)
    return result.ToList();

Rafael

[IT-Als]

Hi Rafael,

I know they write this in the docs..but as you have already migrated to the new Visual Designer I don't see a problem.

In fact, not all functionality is available in the new Context API yet.. For example the Flush() method. Still you can gain access to the underlying ObjectScope instance by using the described method until it is available in the Context API.

Regards

Henrik

[Damyan Bogoev]

Hi Rafael Amaral,

1. You could expose the IObjectScope instance that is used internally by extending you OpenAccessContext class:

public partial class ContextClassName
{
    public IObjectScope Scope
    {
        get
        {
            return this.GetScope();
        }
    }
}

2. Dynamic Linq allows you to pass strings to the LINQ methods and you can dynamically create your LINQ queries. We have code library example which demonstrates how to use Dynamic Linq with Telerik OpenAccess ORM. 

Hope that helps.

Greetings,
Damyan Bogoev
the Telerik team

[Rafael]

Just to tell that i finally used Henrik's solution in roder to do FullTextSearch statements using "Contains".
It works just fine.

Perhaps in a future version of Openaccess, access to IObjectScope while using the Domain Model could be integrated more directelly?

Thanks anyways for you help guys!

Rafael

[IT-Als]

Hi Rafael,

Glad I could help. I would be happy (and probably other visitors, too) if you could mark the post as an Answer. Makes it a lot easier to find the solution among the posts.

I think what is exposed in the ObjectScope now will be exposed in the new Context API, too... over time.
Can anyone from Telerik elaborate on this?

Regards

Henrik

[Alexander]

Hi guys,

Yes, definitely we will extend the context API but I am not able to give you a time-frame when the whole object scope functionality will be covered. I guess we will start with the functionality that is most frequently used, based on the user cases we observe through the support channels.

Sincerely yours,
Alexander
the Telerik team

[IT-Als]

Hi Alexander,

Thanks for the update... as long as we have the scope "at hand" there's no problem :-)

/Henrik