LINQ-TO-SQL query not working with CSV file

Question

I'm reading a simple csv file from my program. Here's what my csv file looks like:

NASDQ,O,
OTC,O,
NYSE,N,
TSE,T,

Here's my code to read the csv file:

string csvFile = @"x:\tech\SQL_IntlPricing\ExchangeLookup.csv";
string[] csvLines = File.ReadAllLines(csvFile);

var csvValues = csvLines
    .Select(l => new { 
        Exchange = l.Split(',').First(), 
        Lookup = l.Split(',').Skip(1).First ()});

So far, everything is fine with the code. I'm using the following LINQ query:

from comp in Companies
where !comp.Coverage_status.Contains("drop")
select new
{
    FSTick = string.Format("{0}-{1}", comp.Ticker,
                csvValues
                    .Where(v => v.Exchange.Contains(comp.Exchange))
                    .Select(v => v.Lookup).FirstOrDefault())
};

But I'm getting the following error:

NotSupportedException: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

Basically, I'm trying to get the following results:

AAPL-O
MSFT-O

Is there a way for me to achieve the results I want using my LINQ query?

Can you post what Companies is/its assignment ? It looks like linq-to-sql will not support referencing the collection (sequence) which is being built by the linq statement itself (comp here). I would convert the whole expression to linq extension methods equivalent. — Veverke
– Veverke, Commented May 30, 2016 at 14:42
I'm not sure if I follow. Companies is an SQL table in my database. What exactly are you asking for? — inquisitive_one
– inquisitive_one, Commented May 30, 2016 at 14:45
I added my answer but after seeing that Cetin Bazoz did almost the same (I think his is better due the AsEnumerable(), so I deleted mine and upvoted his. I am going to temporarily undelete it in case it might help you. His solution does not do it ? — Veverke
– Veverke, Commented May 30, 2016 at 16:10
No. Unfortunately, I'm not getting the results that I need. I don't get any errors, though. — inquisitive_one
– inquisitive_one, Commented May 30, 2016 at 16:14

Cetin Basoz · Accepted Answer · 2016-05-30 14:59:39Z

2

If Companies are not a lot, then the simple solution would be:

    from comp in Companies.Where(c => !c.Coverage_status.Contains("drop")).AsEnumerable()
select new
{
    FSTick = string.Format("{0}-{1}", comp.Ticker,
                csvValues
                    .Where(v => v.Exchange.Contains(comp.Exchange))
                    .Select(v => v.Lookup).FirstOrDefault())
};

Otherwise you could do the filtering there like;

    from comp in Companies.Where( c => 
      csvValues.Select(cs => cs.Exchange).Contains(comp.Exchange) &&
      !c.Coverage_status.Contains("drop")
    ).AsEnumerable()
    select new
    {
        FSTick = string.Format("{0}-{1}", comp.Ticker,
                    csvValues
                        .Where(v => v.Exchange.Contains(comp.Exchange))
                        .Select(v => v.Lookup).FirstOrDefault())
    };

edited May 30, 2016 at 14:59

answered May 30, 2016 at 14:54

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

inquisitive_one Over a year ago

The Companies table has about 2000 companies. Let met start with the 2nd option first. I get an error: The name 'comp' doesn't exist in the current context. The first option didn't give me any errors. However, I'm not getting the results that I want. I get the US companies working correctly, but not international, like TSE. Here's what I'm getting for my results: AAPL-O, ALA-. Note: ALA is listed on the TSE exchange. So, I should be getting ALA-T.

inquisitive_one Over a year ago

I tried your update and I'm still not getting the results that I want. Additionally, I thought the problem only affected internationals, but I'm not getting the OTC exchange as well. Here's what I get now: AAPL-O, ALA-, AMZN-. I should be getting: AAPL-O, ALA-T, AMZN-O.

Cetin Basoz Over a year ago

I only corrected your code. I don't have your data. If you provide your data as code then we could look into what is it.

inquisitive_one Over a year ago

Your code is right. I had to trim the Exchange field from the table. I didn't realize there were spaces. Thanks for all the help.

Veverke · Accepted Answer · 2016-05-30 16:21:46Z

0

Following my comment above, if converting the linq-sql expression into its extension methods syntax form is ok, you could do the following:

I created a list of companies just for the sake of the example. Company was defined as

   public class Company
    {
        public string Coverage_status { get; set; }
        public string Exchange { get; set; }
        public string Ticker { get; set; }
    }

Here's a full sample of how the code will would look like:

 List<string> csvLines = new List<string>
    {
        "NASDQ,O,",
        "OTC,O,",
        "NYSE,N,",
        "TSE,T,"
    };

    var csvValues = csvLines
    .Select(l => new
    {
        Exchange = l.Split(',').First(),
        Lookup = l.Split(',').Skip(1).First()
    });

    List<Company> companies = new List<Company>
    {
        new Company { Coverage_status = "aaa", Ticker = "123", Exchange = "NASDQ"},
        new Company { Coverage_status = "1521drop422", Ticker = "1251223", Exchange = "aaaaaaaa"},
        new Company { Coverage_status = "f2hdjjd", Ticker = "15525221123", Exchange = "TSE"}
    };

    var result = companies
        .Where(c => !c.Coverage_status.Contains("drop"))
        .Select(n => new
        {
            FSTick = string.Format("{0}-{1}", n.Ticker,
                csvValues
                    .Where(v => v.Exchange.Contains(n.Exchange))
                    .Select(v => v.Lookup).FirstOrDefault())
        });

    foreach (var r in result)
        Console.WriteLine(r.FSTick);

For the record, this code is definitely not performance-wise.

Output:

edited May 30, 2016 at 16:21

answered May 30, 2016 at 14:54

Veverke

11.6k6 gold badges58 silver badges109 bronze badges

1 Comment

inquisitive_one Over a year ago

Odd! I tried your suggestion with 1 addition:

var companyList = Companies.Select(c => new Company { Ticker = c.Ticker.Trim(), Exchange = c.Primary_exchange.Trim(), Coverage_status = c.Coverage_status  });

. I'm getting my original error now.

Collectives™ on Stack Overflow

LINQ-TO-SQL query not working with CSV file

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related