Q2 2010 Data Model and query in SQL

11 posts, 1 answers
  1. Rafael
    Rafael avatar
    32 posts
    Member since:
    Jul 2010

    Posted 18 Aug 2010 Link to this post

    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
  2. Shawn Krivjansky
    Shawn Krivjansky avatar
    86 posts
    Member since:
    Jan 2010

    Posted 19 Aug 2010 Link to this post

    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.
  3. DevCraft banner
  4. Answer
    TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 19 Aug 2010 Link to this post

    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
  5. Rafael
    Rafael avatar
    32 posts
    Member since:
    Jul 2010

    Posted 19 Aug 2010 Link to this post

    @ 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
  6. Rafael
    Rafael avatar
    32 posts
    Member since:
    Jul 2010

    Posted 19 Aug 2010 Link to this post

    @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
  7. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 19 Aug 2010 Link to this post

    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
  8. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 19 Aug 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  9. Rafael
    Rafael avatar
    32 posts
    Member since:
    Jul 2010

    Posted 23 Aug 2010 Link to this post

    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


  10. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 24 Aug 2010 Link to this post

    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
  11. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 26 Aug 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  12. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 26 Aug 2010 Link to this post

    Hi Alexander,

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

    /Henrik
Back to Top
DevCraft banner