CODEWITHSUNDEEP
asp.netlinq-to-sqlc#-4.0
Matt
Matt

Reputation: 1616

Correctly structuring this LinqToSql code

Normally I use stored procedures / work in SQL so apologies if I get the terminology slightly off here..

I have a database, with 3 seperate tables, and I need to search multiple fields in each of the 3 tables.

Im sure that I am not doing this the mose effective way, initially I am trying to do it in simple seteps to understand it.

I have the following;

    var foo1 = entities.table1.Where(a => a.bodyText.Contains(searchString) || a.pageTitle.Contains(searchString));
    var foo2 = entities.table2.Where(b => b.newsArticle.Contains(searchString) || b.newsArticle.Contains(searchString));
    var foo3 = entities.table3.Where(c => c.ImageDescriptionContains(searchString));

I need to combine all these results into a single repeater for display.

At this point all 3 sets of data are in seperate, unique collections of anonymous data. So whats the best way of converting these into a single coherent bindable source?

I was thinking of itereating through each list in turn, pulling out the fields I need to display and putting them in a new class, then binding a lsit of these classes to the repeater.

But it all seems a bit clunky to me.

Is there a way of doing the search across all 3 tables in one go, and returning just the fields I need from each table, with a common name (i.e. in SQL I could write 

    select b.newsArticle as myText,

or 

    select newsArticle, ''

to return the news article and an empty string).

Upvotes: 1

Views: 43

Answers (2)

RePierre
RePierre

Reputation: 9566

To get all the results in one go you'll need to define a common class that will be used by all three queries to store the result. This class may be as well anonymous but I'll name it just for clarity.

class Data
{
    public string Text{ get; set;}
}

Now, in your code you'll fetch instances of Data from database and you can use Union:

using( var entities = new YourDataContext)
{
    var foo1 = entities.table1
                       .Where(a => a.bodyText.Contains(searchString) || 
                                   a.pageTitle.Contains(searchString))
                       .Select(a => new Data{ Text = a.bodyText});
    var foo2 = entities.table2
                       .Where(b => b.newsArticle.Contains(searchString) || 
                                   b.newsArticle.Contains(searchString))
                       .Select(b => new Data{ Text = b.newsArticle});
    var foo3 = entities.table3
                       .Where(c => c.ImageDescription.Contains(searchString))
                       .Select(c => new Data{ Text = c.ImageDescription});
    return foo1.Union(foo2).Union(foo3);
}

Upvotes: 1

Daniel Elliott
Daniel Elliott

Reputation: 22857

This would combine:

var foos = foo1.ToList();
foos.AddRange(foo2);
foos.AddRange(foo3);

To get just what you want:

var myExtractedValues = foos.Select(x => new { 
         Article = !string.IsNullOrEmpty(x.newsArticle)) 
                           ? x.newsArticle 
                           : string.Empty});

I have used an anonymous type here but you could swap the new {} with a type of your own.

I reverse the operator on the IsNullOrEmpty but that is just a personal preference (I prefer how is reads.)

Upvotes: 1

Related Questions