Help with Entity Framework query

HungryHippos

HungryHippos

HungryHippos

Soldato
Joined
25 Mar 2004
Posts
15,938
Location
Fareham
Hi all,

I am doing some basic entity framework sql querying at the minute but have a couple of questions.

1) How can I select fields dynamically?

i.e in SQL when you write:

SELECT FieldA, FieldB
FROM Table

At the moment I am declaring all the fields I want in my query, would be awesome if I could base the queried fields based on some user inputs/controls on my webform.

2) Filtering/Ordering on a field that is not in the select statement?

Right now you can see I am adding filters (a simple example of one). If I don't include the column in my select list it complains about it.

In SQL I could query like this:

SELECT FieldA, FieldB
FROM TABLE
WHERE FieldC = 'SomeString'
ORDER BY FieldD

Doesn't seem that simple here, but maybe I am missing something!

Full code example below.

Code: 
using (var Context = new ProjectEntities())
{
    // Base Query
    var Query = from user in Context.Users
        select new {
            user.CompanyName,
            user.Mail
        };
    
    // Add Filters  
    if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text)))
    {
        Query = Query.Where(u => u.CompanyName.StartsWith(SearchCompanyNameTextBox.Text));
    }

    // Order the Query
    Query = Query
        .OrderBy(u => u.Mail);

    // Set the Record Count
    GlobalVars.TotalRecords = Query.Count();

    // Add Paging
    Query = Query
        .Skip(GlobalVars.Skip)
        .Take(GlobalVars.Take);

    // GridView Datasource Binding
    GridViewResults.DataSource = Query;
    GridViewResults.DataBind();

    // Show/Hide Columns
    SetColumns(GridViewResults);
}

Quick note: I am not a developer by trade, I learn enough to be able to do what I need to do really so I may not have all the knowledge I need, but I get by OK.
 
Last edited:
You're probably better off just selecting everything for now (replace select new user.CompanyName, user.Mail} with select user) otherwise it looks like you're going to need to use dynamic LINQ. You can always create methods to sit between the front end and the data which strips out the columns you don't need and returns a simpler objects with less fields if you want (view model concept)

As for your filtering/ordering: combine it all into a single statement then you get access to everything:

Code: 
var Query = from user in Context.Users
			where SearchCompanyNameTextBox.Text == string.Empty || user.CompanyName.StartsWith(SearchCompanyNameTextBox.Text)
			orderby user.Mail
			select new
					   {
						   user.CompanyName,
						   user.Mail
					   };

GlobalVars.TotalRecords = Query.Count();

var pagedQuery =
	Query.Skip(GlobalVars.Skip)
	     .Take(GlobalVars.Take);

// GridView Datasource Binding
GridViewResults.DataSource = pagedQuery;
GridViewResults.DataBind();

// Show/Hide Columns
SetColumns(GridViewResults);
 
Thanks Pho, it's just a shame I would need to return all columns and then filter it out at the client side, this was not something I was able to achieve easily with the GridView and AutoGenerate columns being true.

I had to make that false in the end and add BoundColumns so I could easily iterate through the column collection.

With respects to the filters I will be needing to add more so can't easily add them into the same query, that one is kind of a placeholder :)

orderby will probably also be something which could be different, just I haven't coded that element in yet.

One of the tables I am querying will have 11 million rows ~ and 20 quite large columns so it would have been nicer if I could have returned less data to speed the querying up (yes I have indexes, but still :P)).
 
I can't stand the LINQ syntax. Much prefer:

Code: 
Context.Users
  .Where(each => each.CompanyName.StartsWith(SearchCompanyNameTextBox.Text) || string.IsEmptyOrNull(SearchCompanyNameTextBox.Text))
  .OrderBy(each => each.Mail)
  .Select(each => new { each.CompanyName, each.Mail });

:)

P.S. You will not be pulling all columns. The above query will not "execute" until you begin to iterate the values. (i.e. by using something that calls GetEnumerator() such as a foreach loop, ToList() or Single()/First() etc.)
 
Last edited:
Thanks all,

I've taken this a bit further and started down the dynamic linq path.

First of all I imported the using System.Linq.Dynamic class from the sample pages that came with VS 2010, so I can add this into my page:

using System.Linq.Dynamic;

Then I changed my query like this:

Code: 
private void FetchData()
{
    using (var Context = new ProjectEntities())
    {
        var Query =
            Context.Users
            .Join(Context.UserStats,            // Table to Join
                u => u.msExchMailboxGuid,       // Column to Join From
                us => us.MailboxGuid,           // Column to Join To
                (u, us) => new                  // Alias names from Tables
                {
                    u,
                    us
                })
            .Join(Context.TechContacts,         // Table to Join
                u => u.u.UserPrincipalName,     // Column to Join From
                tc => tc.UPN,                   // Column to Join To
                (u, tc) => new                  // Alias names from Tables
                {
                    u = u,
                    tc = tc
                })
               .Where(u => true)
               .OrderBy("u.u.CompanyName")
               .Select("New(u.u.CompanyName,tc.UPN,u.us.TotalItemSize)");
        
        // Add Extra Filters
        if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text)))
        {
           
            Query = Query.Where("u.CompanyName.Contains(@0)", SearchCompanyNameTextBox.Text);
        }

        // Set the Record Count
        GlobalVars.TotalRecords = Query.Count();

        // Add Paging
        Query = Query
            .Skip(GlobalVars.Skip)
            .Take(GlobalVars.Take);

        // GridView Datasource Binding
        GridViewMailboxes.DataSource = Query;
        GridViewMailboxes.DataBind();
    }
}

Now I can Order by a column by declaring ColumnName Descending or Ascending, and I can also Select fields dynamically by building a string like u.u.ColumnName1, u.u.ColumnName2 etc, most things are working as expected.

The problem is, I don't think the join is working optimally :P

In SQL I can join like this:

SELECT u.Column1, u.Column2, us.Column1, tc.Column1
FROM Users AS u
INNER JOIN UserStats AS us
ON u.IDColumn = us.IDColumn
INNER JOIN TechContacts AS tc
ON u.IDColumn = tc.IDColumn

With that I can modify my SELECT statement as applicable to return the columns, and the columns from each table have a prefix for the alias I used to declare them, so in case of duplicate column names on different tables it doesn't matter.

On my select statement I am having to delve two levels deep i.e. u.u.CompanyName. I'm not totally sure what's going on here which causes it to work like it is, but if I could perform my Join and then have a prefix for each entity it would be much cleaner for me to work with.

If anyone can help with this it would be awesome!
 
If you want to see what it's actually doing in the background your best bet is probably to open up the SQL Profiler and see what it's executing on your DB and start tweaking from there.

Still it's probably just easier to return everything :p.
 
I did just that, and it was a bit of a mess.

I posted about this over on StackOverflow. I think Linq is not really good for what I want to do here (where each element in the query is dynamic). So I posed the question over there:

http://stackoverflow.com/questions/9655580/linq-entity-framework-versus-sql-for-dynamic-data-paging

You can see what I came up with.

The SQL behind this Linq query came out like this. It was not pretty:

Code: 
SELECT TOP (20) 
[Project1].[C1] AS [C1], 
[Project1].[objectGuid] AS [objectGuid], 
[Project1].[msExchMailboxGuid] AS [msExchMailboxGuid], 
[Project1].[CompanyName] AS [CompanyName], 
[Project1].[ResellerOU] AS [ResellerOU], 
[Project1].[DisplayName] AS [DisplayName], 
[Project1].[MBXServer] AS [MBXServer], 
[Project1].[MBXSG] AS [MBXSG], 
[Project1].[MBXDB] AS [MBXDB], 
[Project1].[MBXWarningLimit] AS [MBXWarningLimit], 
[Project1].[MBXSendLimit] AS [MBXSendLimit], 
[Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit], 
[Project1].[extensionAttribute10] AS [extensionAttribute10], 
[Project1].[legacyExchangeDN] AS [legacyExchangeDN], 
[Project1].[UserPrincipalName] AS [UserPrincipalName], 
[Project1].[Mail] AS [Mail], 
[Project1].[lastLogonTimestamp] AS [lastLogonTimestamp], 
[Project1].[createTimeStamp] AS [createTimeStamp], 
[Project1].[modifyTimeStamp] AS [modifyTimeStamp], 
[Project1].[altRecipient] AS [altRecipient], 
[Project1].[altRecipientBL] AS [altRecipientBL], 
[Project1].[DeletedDate] AS [DeletedDate]
    FROM ( SELECT [Project1].[objectGuid] AS [objectGuid],
        [Project1].[msExchMailboxGuid] AS [msExchMailboxGuid],
        [Project1].[CompanyName] AS [CompanyName],
        [Project1].[ResellerOU] AS [ResellerOU],
        [Project1].[DisplayName] AS [DisplayName],
        [Project1].[MBXServer] AS [MBXServer],
        [Project1].[MBXSG] AS [MBXSG],
        [Project1].[MBXDB] AS [MBXDB],
        [Project1].[MBXWarningLimit] AS [MBXWarningLimit],
        [Project1].[MBXSendLimit] AS [MBXSendLimit],
        [Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit],
        [Project1].[extensionAttribute10] AS [extensionAttribute10],
        [Project1].[legacyExchangeDN] AS [legacyExchangeDN],
        [Project1].[UserPrincipalName] AS [UserPrincipalName],
        [Project1].[Mail] AS [Mail],
        [Project1].[lastLogonTimestamp] AS [lastLogonTimestamp],
        [Project1].[createTimeStamp] AS [createTimeStamp],
        [Project1].[modifyTimeStamp] AS [modifyTimeStamp],
        [Project1].[altRecipient] AS [altRecipient],
        [Project1].[altRecipientBL] AS [altRecipientBL],
        [Project1].[DeletedDate] AS [DeletedDate],
        [Project1].[C1] AS [C1],
        row_number() OVER (ORDER BY [Project1].[CompanyName] ASC) AS [row_number]
            FROM ( SELECT 
                [Extent1].[objectGuid] AS [objectGuid], 
                [Extent1].[msExchMailboxGuid] AS [msExchMailboxGuid], 
                [Extent1].[CompanyName] AS [CompanyName], 
                [Extent1].[ResellerOU] AS [ResellerOU], 
                [Extent1].[DisplayName] AS [DisplayName], 
                [Extent1].[MBXServer] AS [MBXServer], 
                [Extent1].[MBXSG] AS [MBXSG], 
                [Extent1].[MBXDB] AS [MBXDB], 
                [Extent1].[MBXWarningLimit] AS [MBXWarningLimit], 
                [Extent1].[MBXSendLimit] AS [MBXSendLimit], 
                [Extent1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit], 
                [Extent1].[extensionAttribute10] AS [extensionAttribute10], 
                [Extent1].[legacyExchangeDN] AS [legacyExchangeDN], 
                [Extent1].[UserPrincipalName] AS [UserPrincipalName], 
                [Extent1].[Mail] AS [Mail], 
                [Extent1].[lastLogonTimestamp] AS [lastLogonTimestamp], 
                [Extent1].[createTimeStamp] AS [createTimeStamp], 
                [Extent1].[modifyTimeStamp] AS [modifyTimeStamp], 
                [Extent1].[altRecipient] AS [altRecipient], 
                [Extent1].[altRecipientBL] AS [altRecipientBL], 
                [Extent1].[DeletedDate] AS [DeletedDate], 
                1 AS [C1]
                FROM   [dbo].[Users] AS [Extent1]
                INNER JOIN [dbo].[UserStats] AS [Extent2] ON [Extent1].[msExchMailboxGuid] = [Extent2].[MailboxGuid]
                INNER JOIN [dbo].[TechContacts] AS [Extent3] ON [Extent1].[UserPrincipalName] = [Extent3].[UPN]
            )  AS [Project1]
    )  AS [Project1]
WHERE [Project1].[row_number] > 120
ORDER BY [Project1].[CompanyName] ASC

Now the next challenge is to call my tableadapter that contains my stored proc from my c# codebehind.

At the moment I am using the GUI but I think I can take advantage of keeping better granular control by using my own frontend elements.

I am currently putting a tableadapter into an .xsd dataset object. Then on my frontpage I have an ObjectDataSource that binds into this tableadapter. Lastly my gridview databinds to my ObjectDataSource.

Should I be doing something similar in pure codebehind here?
 
Actually it's not that bad, it just looks bad because it has a lot of unnecessary words (e.g. repeating [Project1] each time). It's having to use subqueries because you're filtering with .skip() and .take() which again looks like it doubles the size of the query because it explicitly lists all the columns again.

Have you tried just combining it down into one big query rather than lots of little queries in your code? If in doubt view the execution plan in management studio for the query, it probably performs adequately.

Have a look at this guide for how to bind entity stuff to your controls.

What you might want to do is have your FetchData method return an IEnumerable<SomeClassWhichYouCanCopyAllTheFetchedFieldsTo> (an array of objects made from the queries' rows basically) and then have another method to actually bind that to the gridview. It keeps it cleaner by separating it all out.
 
You must log in or register to reply here.
Back
Top Bottom