How to return query results from method that uses LINQ to SQL

Question

Here is the code I'm working with, I'm still a bit new to LINQ, so this is a work in progress. Specifically, I'd like to get my results from this query (about 7 columns of strings, ints, and datetime), and return them to the method that called the method containing this LINQ to SQL query. A simple code example would be super helpful.

using (ormDataContext context = new ormDataContext(connStr))
{
    var electionInfo = from t1 in context.elections
               join t2 in context.election_status
               on t1.statusID equals t2.statusID
               select new { t1, t2 };
}

(In this case, my query is returning all the contents of 2 tables, election and election_status.)

Answer 1

return electionInfo.ToList();

Answer 2

You'll need to create classes that have the same structure as the anonymous types. Then, instead of "new { t1, t2 }", you use "new MyClass(t1, t2)".

Once you have a named class, you can pass it all over the place as you were hoping.

Answer 3

You cannot return an anonymous type from a method. It is only available within the scope in which it is created. You'll have to create a class instead.

Answer 4

Specifically, I'd like to get my results from this query (about 7 columns of strings, ints, and datetime), and return them

Hi, the problem you've got with your query is that you're creating an anonymous type. You cannot return an anonymous type from a method, so this is where you're going to have trouble.

What you will need to do is to create a "wrapper" type that can take an election and an election_status and then return those.

Here's a little sample of what I'm talking about; as you can see I declare a Tuple class. The method that you will wrap your query in returns an IEnumerable.

I hope this helps :-)

class Tuple
{
    Election election;
    Election_status election_status;

    public Tuple(Election election, Election_status election_status)
    {
        this.election = election;
        this.election_status = election_status;
    }
}

public IEnumerable<Tuple> getElections()
{
    IEnumerable<Tuple> result = null;

    using (ormDataContext context = new ormDataContext(connStr))
    {
        result = from t1 in context.elections
                 join t2 in context.election_status
                 on t1.statusID equals t2.statusID
                 select new Tuple(t1, t2);
    }
}

UPDATE

Following from NagaMensch's comments, a better way to achieve the desired result would be to use the built in LINQ to SQL associations.

If you go to your entity diagram and click on toolbox, you will see 3 options. Class, Association and Inheritance. We want to use Association.

• Click on Association and click on the ElectionStatus entity, hold the mouse button down and it will allow you to draw a line to the Election entity.

• Once you've drawn the line it will ask you which properties are involved in the association. You want to select the StatusId column from the Election entity, and the StatusId column from the ElectionStatus entity.

Now that you've completed your mapping you will be able to simplify your query greatly because the join will not be necessary. You can just access the election status via a brand new property that LINQ to SQL will have added to the Election entity.

Your code can now look like this:

//context has to be moved outside the function
static ExampleDataContext context = new ExampleDataContext();

//Here we can return an IEnumerable of Election now, instead of using the Tuple class
public static IEnumerable<Election> getElections()
{
    return from election in context.Elections
           select election;
}

static void Main(string[] args)
{
    //get the elections
    var elections = getElections();

    //lets go through the elections
    foreach (var election in elections)
    {
        //here we can access election status via the ElectionStatus property
        Console.WriteLine("Election name: {0}; Election status: {1}", election.ElectionName, election.ElectionStatus.StatusDescription);
    }
}

You can also find a "how to" on LINQ to SQL associations here.

Note: It's worth mentioning that if you have an FK relationship set up between your tables in the database; LINQ to SQL will automatically pick the relationship up and map the association for you (therefore creating the properties).

Answer 5

The problem is, that you are creating a anonymous type, hence there is no way to declare a method with this return type. You have to create a new type that will hold your query result and return this type.

But I suggest not to return the result in a new type but return just a colection of election objects and access the election_status objects through the relation properties assuming you included them in your model. The data load options cause the query to include the related election status objects in the query result.

public IList<election> GetElections()
{
    using (ormDataContext context = new ormDataContext(connStr))
    {
        DataLoadOptions dlo = new DataLoadOptions();
        dlo.LoadWith<election>(e => e.election_status);
        context.DeferredLoadingEnabled = false;
        context.LoadOptions = dlo;   
        return context.elections.ToList();
    }
}

Now you can do the following.

IList<election> elections = GetElections();

// Access the election status.
Console.WriteLin(elections[0].election_status);

I general LINQ to SQL could just retrieve the related entities on demand - that is called deferred loading.

ormDataContext context = new ormDataContext(connStr));

IList<election> elections = context.elections.ToList();

// This will trigger a query that loads the election
// status of the first election object.
Console.WriteLine(elections[0].election_status);

But this requires you not to close the data context until you finished using the retrieved objects, hence cannot be used with a using statement encapsulated in a method.